relative references in Macro

[shternm]

I have a question about how to make relative references within VLOOKU
macro work.

For Example:
Dim sht As Worksheet
Dim rng As Range

Set sht = Worksheets("sheet1")

sht.Range("H12") = "=VLOOKUP(B$4,rev,3,FALSE)"
sht.Range("G12") = "=VLOOKUP(B$4,rev,3,FALSE)"

I want the G12 cell to reference C$4 without changing it in the macro
Just as it would if I have the formula in H12 and then drag it over t
G12 instead of using the macro.

Thanks a lot

 

[icestationzbra]

i have not tried this for your specific example (did not paste the cod
and check it if it works), but just try $B$4, $C$4 for absolut
referencing

 

[shternm]

If I make it absolute, I will end up with appoximately 200 lines o
code.

I am trying to avoid changing it in every single formula in the Macro

 

[icestationzbra]

oh, i am sorry, i understood your post in the exact opposite way

 

[icestationzbra]

if you are fairly cognizant of VBA, i suggest that you try this on a
backup copy of your workbook.

use Application.WorksheetFunction.VLookup instead of "=VLOOKUP..."

use Offset(-2, -3) where -2 and -3 mean that you are going to go 2 row
up and 3 columns to the left to place a certain value, or pick up
certain value. change the numbers as they would suit your case. if yo
need to go thrice down and twice right, you would write it as Offset(3
2).

for example:

ActiveCell.Offset(-2, -3).Value = ActiveCell.Value

would place a value that is in E8, into cell B6. you can similarl
extract values from cell if your offset does not change at all.

if i had the data, i could have tested these suggestions before givin
it to you. right now, i am not able to think it up