In Excel, how do I find one word in a set of text in a cell? and how to pest dat

[roypabitra]

I have VBA code on which I can find a exact word and pest it another sheet but I want to search and pest not exact word like the following


pabitra 100

rupa and pabitra 200

pabitra and sampa 30

rupa and sampa 400

sampa and sameer 500

sampa 600

rupa 700


now I want to copy data on which included pabitra but my VBA search and pest exeact word on which Only pabitra


Please help me

 

[Debraj]

Hi Pabitra...


Jodi VBA code ta paste koro to bhalo hoi..


Regards,

Deb..


"If you PEST your VBA CODE.. then it will better for us.. "

 

[roypabitra]

Dear Debraj Sir

Please find the following VB code :-

[pre]

Sub copy_paste_data_from_one_sheet_to_another()
'Let's start at row 2. Row 1 has headers
x = 4
Dim myName As String
Dim myNameF As String
Dim cPart As Range
Dim Wk1 As Worksheet
Dim Wk2 As Worksheet

'Dim nextRow As Long
'Dim oCol As Long

'Dim myCopy As Range
'Dim myTest As Range
'Dim myTest1 As Range

'Set inputWks = Worksheets("Input")
'Set historyWks = Worksheets("KOLKATA")

Set Wk1 = Worksheets("booking")
Set Wk2 = Worksheets("Check List")

Wk2.Activate
'LastRow = Range("D65536").End(xlUp).Row + 1
Range("A5" & ":N65536").ClearContents

myNameF = Application.InputBox("  Enter : -  B I L L   N O ")
'myName = Application.Selection("Checklist")
myName = UCase(myNameF)
'Worksheets("wk1").Activate
'Start the loop
Do While Cells(x, 8) <> ""
'Look for name
If Cells(x, 8) = myName Then
'copy the row if it contains 'myName'
Wk1.Rows(x).Copy
'Go to sheet2. Activate it. We want the data here
Wk2.Activate
'Find the first empty row in sheet2
erow = Wk2.Cells(Rows.Count, 8).End(xlUp).Offset(1, 0).Row
'erow = Wk2.Cells(Rows.Count, 8).Offset(3, 0).Row
'Paste the data here
Application.DisplayAlerts = False
ActiveSheet.Paste Destination:=Wk2.Rows(erow)
Application.DisplayAlerts = True
End If

'go to sheet1 again and actvate it
Wk1.Activate
'Loop through the other rows with data
x = x + 1
Loop

Wk2.Activate
End Sub

[/pre]

 

[Debraj]

Hi Pabitra,


Change this line ..

[pre]

Do While Cells(x, 8) <> ""
'Look for name[/pre]
       [b]If Cells(x, 8) = myName Then[/b]


to

if INSTR(Cells(x, 8),myName,1)>0 then

Check and confirm..


Regards,

Deb

 

[roypabitra]

Respected Sir,


As when we change the line "if INSTR(Cells(x, 8),myName,1)>0 then" instated of "If Cells(x, 8) = myName Then" it shows error code 13 type mismatch and request you please advice me how to attach this worksheet file.


again I posted the VBA code for further reference

Sub copy_paste_data_from_one_sheet_to_another()

'Let's start at row 2. Row 1 has headers

x = 1

Dim myName As String

Dim myNameF As String

Dim cPart As Range

Dim Wk1 As Worksheet

Dim Wk2 As Worksheet


Set Wk1 = Worksheets("Sheet1")

Set Wk2 = Worksheets("Sheet2")


Wk2.Activate

Range("A2" & ":N65536").ClearContents

Wk1.Activate

myName = Application.InputBox(" Enter : - N A M E ")

'myName = UCase(myNameF)

'Start the loop

Do While Cells(x, 1) <> ""

'Look for name

If InStr(Cells(x, 1), myName, 1) > 0 Then

'If Cells(x, 1) = myName Then

'copy the row if it contains 'myName'

Wk1.Rows(x).Copy

'Go to sheet2. Activate it. We want the data here

Wk2.Activate

'Find the first empty row in sheet2

erow = Wk2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'Paste the data here

Application.DisplayAlerts = False

ActiveSheet.Paste Destination:=Wk2.Rows(erow)

Application.DisplayAlerts = True

End If

'go to sheet1 again and actvate it

Wk1.Activate

'Loop through the other rows with data

x = x + 1

Loop


Wk2.Activate

End Sub


Thanks & help me


Pabitra

 

[Debraj]

Hi Pabitra,


To upload file.. you have use any FILE TRANSFER site.. List and detail mentioned below..

http://chandoo.org/forums/topic/posting-a-sample-workbook


Regards,

Deb

 

[Debraj]

Hi Pabitra,


Sorry, Its my fault..


change

If Cells(x, 8) = myName Then

to

if INSTR(1,Cells(x, 8),myName,1)>0 then


In your case (x,1)..


I just learned a New thing Today.. In Instr if COMPARE METHOD is provided, then Starting character is mandatory..


Regards,

Deb