hi...

i am doing a vlookup and usin a formula for that. i want to remove the
hardcoded values to as far extent i can. So that if any new column or new
row is added then the macro shouldnt fail. Which normally everyone does.

the below code is a small relevant part of my code. Its working perfectly
fine.
Set rng3 = Cells.Find("Package")
If rng3 Is Nothing Then MsgBox "Could not locate column Package in the
worksheet "" & DataWorksheet1Name & "" in workbook " & wkBkName & "."
c1 = Cells(rng3.Row + 1, rng3.Column).Address 'STARTING RANGE

Set rng4 = Cells.Find("Q4")
If rng4 Is Nothing Then MsgBox "Could not locate column ""Q4"" in the
worksheet " & DataWorksheet1Name & " in workbook " & wkBkName
c2 = Cells(tcrLastCell, rng4.Column).Address 'ENDING RANGE

For z = lastCol3 + 1 To lastCol4
rowSt = LNewRng.Row + 1
While rowSt <= LastCellNum - 1
Cells(rowSt, z).Value = "=( VLOOKUP(" & Cells(rowSt,
packRng.Column).Address & "," & ext & "!" & c1 & ":" & c2 & ","
& f1 & ",FALSE)" & ")"
rowSt = rowSt + 1
Wend
next z

teh above formula gets resolved as below in each cell.
=( VLOOKUP($F$10,'C:\Documents and Settings\ASSY\Desktop\excel-vba\Input
files\[TCR2003_Assembly_0305.xls]TCR2003 by packages'!$B$4:$G$238,4,FALSE))

$F$10 comes because i am using the ".address ". The requiremetn is thati
need to have the reference of the cell i am using hte value of. So i cannot
use ".value". But now the requirement is that what if another row I insert a
row inbetween $F$10 and $F$11.... this $F$11 does cahnge to $F$12/.... but
the person cannot copy the vloopup formala from any of the cell and use
it.... like in this case if i copy the row 10 formula then I get the same
values for that same $F$10 value. where i want that whatever is inserted in
$F$11 teh value for vlookup should cahnge which is not happening.

am i missing something??? or do i need to use anyother object.... which
would solve my problem.

thanks to those who read this and respond.

Monika