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,081,454
Messages
5,358,771
Members
400,513
Latest member
sdrowsick

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top