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

You are missing an understanding of Absolute and relative addressing. If
you want an address to be relative and adjust as rows are inserted, you
would remove the $$

..Address(0,0)

Perahaps that is all you need. It is unclear what you want if someone
copies and pastes the formula - but you can't have it both ways - acting
relative for one action and absolute for another.

--
Regards,
Tom Ogilvy


"monika" wrote in message
...
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

If I understand correctly, try using .Address(False,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
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

Cool....excellent..

both the things work the same and as per my requirement..

thanks a lot!
"Bob Phillips" wrote in message
...
If I understand correctly, try using .Address(False,False)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"monika" wrote in message
...
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