.address / .value

[monika]

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

 

[Tom Ogilvy]

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.

 

[Bob Phillips]

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]

Cool....excellent..

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

thanks a lot!

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)

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