I need to find a value such as ABC123 in a cell and select the cell.
if no match is found then I want to do something else
ABC123 is in fact a variable but I know how to find a variable

A recorded macro gives the following to find the cell and select it but does not show me the code for if no match then...


Cells.Find(What:="ABC123", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate



have tried a search on the internet but not found what I'm looking for
smiler44

not convinced this is right but it's better


On Error GoTo nomatch
'
Cells.Find(What:="ABC123", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

nomatch:
MsgBox ("here")

Not sure what you are really attempting, I would expect that to fail with a block set or something similar error not sure what the exact phrase is.
The problem with recorded code is that it sometimes leeds us astray.

Are you searching a range, the help shows you how to do that but you need to translate the fixed range to your probably dynamic range.
If you really need to Select the Cell, and in VBA you usually don't, then in the help example c.Select will select it.

Brian

Brian,
i have a column in sheet one and I want to go down that column,A cell by cell looking for the contents in another sheet, sheet2 column C. If I find a match I need to go across 3 columns and copy the contents of the cell. Then go back to my original sheet, sheet1 go across 1 column and paste. If no match is found then I need to insert no match into the cell.

just typing this reply i think i have remembered the code to insert no match and its not activesheet.paste.whatever

smile44

I can never get those things to work with the Find as the addressing across sheets seems difficult, probably me, so I do it this way.

Brian

Sub mycopy()
'Brian warnock
'Note suffix 1 and 2 refers to sheets 1 and sheets2 respectively

Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rownum1 As Long
Dim rownum2 As Long

lastrow1 = Sheets("sheet1").Range("A65536").End(xlUp).Row
lastrow2 = Sheets("sheet2").Range("C65536").End(xlUp).Row
rownum1 = 1 '1st row sheet1

Do
rownum2 = 1 ' 1st row in sheet2
Do Until rownum2 > lastrow2
If Sheets("sheet1").Cells(rownum1, 1) = Sheets("sheet2").Cells(rownum2, 3) Then
GoTo matched
Else
rownum2 = rownum2 + 1
End If
Loop

Sheets("sheet1").Cells(rownum1, 2) = "no match"
'goto next row sheet1
GoTo next1

matched:

Sheets("sheet1").Cells(rownum1, 2) = Sheets("sheet2").Cells(rownum2, 6)

next1:

rownum1 = rownum1 + 1
Loop Until rownum1 > lastrow1


End Sub

Fresh air clears the mind, out walking today and suddenly realised that the code was a bit messy so have tidied it up. These things happen as I get older, the old subconcience thing. :)

Brian

Sub mycopy()
'Brian warnock
'Note suffix 1 and 2 refers to sheets 1 and sheets2 respectively

Dim lastrow1 As Long
Dim lastrow2 As Long
Dim rownum1 As Long
Dim rownum2 As Long

lastrow1 = Sheets("sheet1").Range("A65536").End(xlUp).Row
lastrow2 = Sheets("sheet2").Range("C65536").End(xlUp).Row
rownum1 = 1 '1st row sheet1

Do
rownum2 = 1 ' 1st row in sheet2
Do Until rownum2 > lastrow2
If Sheets("sheet1").Cells(rownum1, 1) = Sheets("sheet2").Cells(rownum2, 3) Then
Sheets("sheet1").Cells(rownum1, 2) = Sheets("sheet2").Cells(rownum2, 6)
'goto next row sheet1
GoTo next1
Else
rownum2 = rownum2 + 1
End If
Loop

Sheets("sheet1").Cells(rownum1, 2) = "no match"

next1:

rownum1 = rownum1 + 1
Loop Until rownum1 > lastrow1

End Sub

Brian,
thank you very much. I was impressed with your version 1. Could I ask a question, not to be impertinent but to learn?
your code has GoTo Next1

Why GoTo next1, why not have the next1 code in place of GoTo next1?

In the mean time I now move to write version 2 of the project!

Smiler44

Vlookup won't work?

Brian,
thank you very much. I was impressed with your version 1. Could I ask a question, not to be impertinent but to learn?
your code has GoTo Next1

Why GoTo next1, why not have the next1 code in place of GoTo next1?

In the mean time I now move to write version 2 of the project!

Smiler44

This code

rownum1 = rownum1 + 1
Loop Until rownum1 > lastrow1

needs to be executed when you either have a match
or
you have searched through sheet2 and have no match

The Loop statement can only exist once therefore both options have to arrive at the same place, one by a Goto the other by dropping through.

Brian

Vlookup won't work?

Vlookup could be made to work using False for the Range parameter and checking for N/A, but I assume that Smiler wants to use VBA so that he doesn't have to fiddle with the worksheet as the data changes.

Brian