Find Text

[PBM]

Have Text In Column A, Need Vba Code To Find Cell With Matching Text In Another Workbook. Can Anyone Help Please.

[duane]

here is a start - this was written to go down a column (the colkm) of text row by row (the j) and find the text in that cell in a range on a different sheet. You would need to switch back and forth between workbooks as well as sheets.

without knowing more of what your are dealing with it is difficult to help more.

For j = startkm To numkm + startkm
Sheets("km").Select
tender = Sheets("km").Cells(j, colkm).Text
Set rng = Sheets("nominated").Range("nom").Find(tender)
If rng Is Nothing Then GoTo badkmtender
nomrow = rng.Row

[PBM]

Thanks Duane, have run out of time today. I will give it a try later & let you know how it went.

[PBM]

Duane, maybe if I explain fully what I am trying to achieve you could help me.
In workbook "REGO" I have registration numbers in cells A5111 to A5250.
These registration numbers also reside in another workbook "DETAIL" somewhere in the range A2 to A8250.
I am struggling trying to write a macro that that will find the text in "REGO" A5111 in the "DETAIL" workbook, copy all the cells to the right (B?? to AE??) and paste them into "REGO" at B5111. Looping through to "REGO" A5250.

[duane]

try this - I have not testted or debugged though

sub textsearch
'
'put this macro in worksheet rego
' change sheet1 here to whatever the right sheet name is
'
Sheets("sheet1").Select
'
' you could make this row range a variable, or a named range
' 
For j = 5111 to 5250
'
Windows("rego").Activate
'
'set variable to text in row j, column 1
'
mytext = Sheets("sheet1").Cells(j, 1).Text
windows("Detail").activate
'
' change sheet2 here to the workshet name
'
sheets("sheet2").activate
'
' better to make the range a named range
'
Set rng = Sheets("sheet2").Range(cells(2,1), cells(8250,1)).Find(mytext)
'
' this handles the case where the text is not found
'
If rng Is Nothing Then GoTo nexttext
'
' if text is found, copy everything to right and paste in rego
'
textrrow = rng.Row
lastcol = cells(rng.row,2).end(xltoright).column
range(cells(rng.row,2), cells(rng.row, lastcol)).copy
windows("rego").activate
cells(j,2).select
application.paste
nexttext:
next j
end sub

[PBM]

Duane, the macro falls over at, "mytext = Sheets("sheet1").Cells(j, 1).Text" with run-time error 1004 - application-defined or object-defined error.
Also question: The "DETAIL" workbook is actually .csv file ie. doesn't have sheets, what code to set the range in this file.
Sorry to bug you again, but as you've no doubt gathered I'm new at this.
Thanks....Peter