Find the Value of a Cell in the spreadsheet

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
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!!


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

Boswell

Board Regular
Joined
Jun 18, 2010
Messages
224
Try this Code:

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

Active Member
Joined
Aug 29, 2008
Messages
336
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!!


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

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

tcnt9176

Board Regular
Joined
Jun 23, 2008
Messages
223
Thanks. I forgot to mention that cell ("CA1") was on another sheet. I tried the code below and got another error.
Code:
Cells.Find(What:=Cells("'Sheet1'!CA1").Value, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
 
Last edited:

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
Thanks. I forgot to mention that cell ("CA1") was on another sheet. I tried the code below and got another error.
Code:
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

Active Member
Joined
Aug 29, 2008
Messages
336
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

Active Member
Joined
Aug 29, 2008
Messages
336
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
 
Last edited:

Forum statistics

Threads
1,085,048
Messages
5,381,390
Members
401,736
Latest member
Karzmo

Some videos you may like

This Week's Hot Topics

Top