ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   New Users to Excel (https://www.excelbanter.com/new-users-excel/)
-   -   syntax question - cell references in VB code (https://www.excelbanter.com/new-users-excel/15769-syntax-question-cell-references-vbulletin-code.html)

needyourhelp

syntax question - cell references in VB code
 
Given the following code (found on this site..)

With ActiveSheet.Range("B:B")
Set FoundCell = .Find(what:="whatever", _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
End With

How do I tell VB to look in Cell H1 for the what:="whatever" ?

ie. I want to use the value found in the Cell H1 not the text "whatever"

I've tried every combination of H1, H1:H1, $H1, Range(H1), Range(H1:H1),
Range("H1"),etc....

All generate compile errors, syntax errors, or runtime errors....

Forgive the simple question, but I'm not VB literate and I don't have a
manual handy.


JE McGimpsey

One way:

With ActiveSheet.Range("B:B")
Set FoundCell = .Find(What:=ActiveSheet.Range("H1").Value, _
After:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End With



In article ,
needyourhelp wrote:

Given the following code (found on this site..)

With ActiveSheet.Range("B:B")
Set FoundCell = .Find(what:="whatever", _
after:=.Cells(.Cells.Count), _
LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
End With

How do I tell VB to look in Cell H1 for the what:="whatever" ?

ie. I want to use the value found in the Cell H1 not the text "whatever"

I've tried every combination of H1, H1:H1, $H1, Range(H1), Range(H1:H1),
Range("H1"),etc....

All generate compile errors, syntax errors, or runtime errors....

Forgive the simple question, but I'm not VB literate and I don't have a
manual handy.



All times are GMT +1. The time now is 09:22 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com