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
 

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

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

ADVERTISEMENT

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

ADVERTISEMENT

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:

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,963
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top