VB code Find a variable value

[owendodd]

Hi,

Below is the macro code for entering a code into a cell (H3) and finding this code value in another sheet. I can only do this as a constant. Would anyone know how i could take the value in as a variable meaning and find the value based on the number entered

ActiveCell.FormulaR1C1 = ""
Sheets("Sep").Select
Range("H3").Select
Selection.Copy
Sheets("SCHEDULE2").Select
Columns("A:A").Select
Selection.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Rows("344:344").Select

 

[Andrew Poulsom]

Do you mean?

Selection.Find(What:=Sheets("Sep").Range("H3").Value, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

 

[lozzablake]

something like this? It assumes there is already a code in H3 and then activates the cell where it founds the code. Gives a message if the code is not found:

Sub TestFind()

    Dim rngTarget As Range
    Dim rngFound As Range
    Dim SearchFor

    Set rngTarget = Sheets("SCHEDULE2").Cells
    SearchFor = Sheets("Sep").Range("H3")

    Set rngFound = rngTarget.Find(SearchFor)
    
    If Not rngFound Is Nothing Then
        rngFound.Activate
    Else
         msgbox "Code not Found"
    End If
End Sub

 

[owendodd]

Yes, something like that, How would i apply this to my code above. I am not a VB programmer so have had problems with this.

Thanks for your assistance!

 

[owendodd]

Hi with that

message above, i am getting a debugger problem for

rngFound.Activate

 

[lozzablake]

put the following line above the rngfound.activate:

 Sheets("SCHEDULE2").Activate

 

[owendodd]

Thanks worked a treat!

 

[owendodd]

Jus another question i would like to do the same with for when i go browse down on column C i select a cell that contains a code. When i run the macro it finds this code on column C on the other worksheet that i have.

Any ideas

 

[lozzablake]

it's exactly the same, but change the following:

1) sheet names to whatever they are

2) replace the SearchFor= line with:

SearchFor = Activecell.value