Find the Value of a Cell in the spreadsheet

[tcnt9176]

I just need to know what is wrong with this code. I am trying to have the macro find whatever is entered into cell ("CA1"). Thanks for your help!!

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

 

[Boswell]

Try this Code:

Sub display_value()

    Dim ws As Worksheet
    Dim val As Variant
    
    Set ws = ActiveWorkbook.Worksheets("Sheet2")
    val = ws.Range("CA1").Value
    
    MsgBox "The value in cell CA1 on worksheet " & ws.Name & " equals " & val
    
End Sub

I took the extra step of specifying what worksheet to look at. If you do not specify what worksheet the active worksheet is used. I think the key statement you are looking for is "Range("CA1").Value"... which will return the value entered in cell "CA1".

 

[gsbelbin]

I just need to know what is wrong with this code. I am trying to have the macro find whatever is entered into cell ("CA1"). Thanks for your help!!

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

How about

    Cells.Find(What:=Cells("CA1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

 

[Boswell]

Oh.. I obviously misunderstood what you were trying to do.

 

[tcnt9176]

Thanks. I forgot to mention that cell ("CA1") was on another sheet. I tried the code below and got another error.

Cells.Find(What:=Cells("'Sheet1'!CA1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

 

[gsbelbin]

Thanks. I forgot to mention that cell ("CA1") was on another sheet. I tried the code below and got another error.

Cells.Find(What:=Cells("'Sheet1'!CA1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate

The What bit should be worksheets("Sheet1").cells("CA1").value

 

[gsbelbin]

The What bit should be worksheets("Sheet1").cells("CA1").value

... or maybe not. Sorry, tried that and it doesn't work. Need to have more of a think.

 

[gsbelbin]

Create a variable and put the value of CA1 into it

x = Worksheets("OtherSheetName").Range("CA1").Value

Then use the variable in your Find statement

What := x

 

[tcnt9176]

AWESOME!! That worked perfectly! Thanks!!