Using the Find&Replace

[hartless43]

Sub FindCellThatContains()

    Columns("AO:AO").Select
    Range("AO28:AO944").Activate
    Selection.Find(What:="241", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
End Sub

I don’t know if anyone has ever delved into something like this. The above is a VBA I created using the Marco Recorder to Find a cell with the value of 241 in Column O and it works as recorded, but without using the Find & Replace feature each time I need a way to pick up the value of cell “N1” which in this case is 241. The value of “N1” changes quite often. I tried changing the “241” to “&N1&” and that doesn’t work. The debugger doesn’t reject it. I need to change the FindWhat:=” “value to what is in cell “N1”. I am working on this experimental thing, so no big deal if no one knows.



Thanks

 

[Skyybot]

Stand by

 

[Skyybot]

Sub FindCellThatContains()

    Columns("AO:AO").Select
    Range("AO28:AO944").Activate
    Selection.Find(What:="241", After:=ActiveCell, LookIn:=xlFormulas2, _
        LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
End Sub

I don’t know if anyone has ever delved into something like this. The above is a VBA I created using the Marco Recorder to Find a cell with the value of 241 in Column O and it works as recorded, but without using the Find & Replace feature each time I need a way to pick up the value of cell “N1” which in this case is 241. The value of “N1” changes quite often. I tried changing the “241” to “&N1&” and that doesn’t work. The debugger doesn’t reject it. I need to change the FindWhat:=” “value to what is in cell “N1”. I am working on this experimental thing, so no big deal if no one knows.



Thanks

So, you need to use the value of Cell N1 and see if it exists in Column O?

 

[hartless43]

Skybot, yes

 

[Skyybot]

Skybot, yes

And the cells are all on the same sheet?

 

[Skyybot]

Try this. Let me know what happens.

Sub FindCellThatContains()
Dim wb As Workbook, sht As Worksheet, nRng As Range, srchRng As Range, nVal As String, goRng
Set wb = ThisWorkbook: Set sht = wb.Sheets(1): Set nRng = sht.Cells(1, 14): Set srchRng = Range(sht.Cells(1, 41), sht.Cells(sht.UsedRange.Rows.Count, 41))
nVal = nRng.Value
Set goRng = srchRng.Find(What:=nVal)
goRng.Select
End Sub

 

[Peter_SSs]

VBA I created using the Marco Recorder to Find a cell with the value of 241 in Column O

From your macro recorder code it appears you are looking in column AO not column O?

Would this do what you want?
(But just change to O if that is the correct column)

Sub FindCellThatContains_v2()
  Range("AO28:AO944").Find(What:=Range("N1").Value, LookIn:=xlFormulas2, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False).Activate
End Sub

 

[hartless43]

Try this. Let me know what happens.

Sub FindCellThatContains()
Dim wb As Workbook, sht As Worksheet, nRng As Range, srchRng As Range, nVal As String, goRng
Set wb = ThisWorkbook: Set sht = wb.Sheets(1): Set nRng = sht.Cells(1, 14): Set srchRng = Range(sht.Cells(1, 41), sht.Cells(sht.UsedRange.Rows.Count, 41))
nVal = nRng.Value
Set goRng = srchRng.Find(What:=nVal)
goRng.Select
End Sub

Thank looks complicated but it works Will keep it on file just in case I need it.

 

[hartless43]

From your macro recorder code it appears you are looking in column AO not column O?

Would this do what you want?
(But just change to O if that is the correct column)

Sub FindCellThatContains_v2()
  Range("AO28:AO944").Find(What:=Range("N1").Value, LookIn:=xlFormulas2, LookAt:=xlPart, MatchCase:=False, SearchFormat:=False).Activate
End Sub

Sorry about that O column and Thanks heap mate for "your" solution. I would have liked very much to visit Austria but that ain't going to happen.

 

[Peter_SSs]

Thanks heap mate for "your" solution.

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.

I would have liked very much to visit Austria ...

BTW, did you want to visit Australia or Austria (or maybe both )?