Reference Cell in VLOOKUP

B

Bishop

Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
 
D

Dave Peterson

I didn't notice that you were putting the formula in a single cell.

You can use:
..range("z" & newrow2).formula = _
or
..cells(newrow2,"Z").formula = _
instead of:
..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

It makes it a little easier to read.

NewRow2 = 6
With Worksheets("Sheet1")
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

.Range("AA" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE))"

.Range("Z" & NewRow2 & ":AA" & NewRow2 + 4).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount
End With

And I didn't see a response to the other thread (yet??), but remember when you
use =indirect(), the sending workbook has to be open.
Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
Click to expand...
 
B

Bishop

When I use ($A" & NewRow2... like you suggest I get an error [end of
statement expected]. I even tried ("$A" & NewRow2... I also tried (""$A"" &
NewRow2... which did put the formula in the spreadsheet but it printed like
this ($A & NewRow2... so the code didn't interpret the variable, it just
printed the variable name. What am I doing wrong?

Dave Peterson said:
I didn't notice that you were putting the formula in a single cell.

You can use:
..range("z" & newrow2).formula = _
or
..cells(newrow2,"Z").formula = _
instead of:
..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

It makes it a little easier to read.

NewRow2 = 6
With Worksheets("Sheet1")
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

.Range("AA" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE))"

.Range("Z" & NewRow2 & ":AA" & NewRow2 + 4).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount
End With

And I didn't see a response to the other thread (yet??), but remember when you
use =indirect(), the sending workbook has to be open.
Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
Click to expand...
Click to expand...
 
D

Dave Peterson

Did you retype my suggestion or did you copy|paste?

I'm betting that you changed something else.


When I use ($A" & NewRow2... like you suggest I get an error [end of
statement expected]. I even tried ("$A" & NewRow2... I also tried (""$A"" &
NewRow2... which did put the formula in the spreadsheet but it printed like
this ($A & NewRow2... so the code didn't interpret the variable, it just
printed the variable name. What am I doing wrong?

Dave Peterson said:
I didn't notice that you were putting the formula in a single cell.

You can use:
..range("z" & newrow2).formula = _
or
..cells(newrow2,"Z").formula = _
instead of:
..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

It makes it a little easier to read.

NewRow2 = 6
With Worksheets("Sheet1")
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

.Range("AA" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE))"

.Range("Z" & NewRow2 & ":AA" & NewRow2 + 4).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount
End With

And I didn't see a response to the other thread (yet??), but remember when you
use =indirect(), the sending workbook has to be open.
Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
Click to expand...
Click to expand...
Click to expand...
 
B

Bishop

I didn't copy|paste until this morning. I merely changed the part I
originally asked you about ($A" & NewRow2...). I went back and copy|pasted
like you suggested and sure enough it works. I went back and tried to find
the descrepencey but couldn't. Your use of the & makes the code look better
anyway. Just out of curiosity what else did you change?

Dave Peterson said:
Did you retype my suggestion or did you copy|paste?

I'm betting that you changed something else.


When I use ($A" & NewRow2... like you suggest I get an error [end of
statement expected]. I even tried ("$A" & NewRow2... I also tried (""$A"" &
NewRow2... which did put the formula in the spreadsheet but it printed like
this ($A & NewRow2... so the code didn't interpret the variable, it just
printed the variable name. What am I doing wrong?

Dave Peterson said:
I didn't notice that you were putting the formula in a single cell.

You can use:
..range("z" & newrow2).formula = _
or
..cells(newrow2,"Z").formula = _
instead of:
..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

It makes it a little easier to read.

NewRow2 = 6
With Worksheets("Sheet1")
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

.Range("AA" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE))"

.Range("Z" & NewRow2 & ":AA" & NewRow2 + 4).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount
End With

And I didn't see a response to the other thread (yet??), but remember when you
use =indirect(), the sending workbook has to be open.

Bishop wrote:

Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

I don't recall.

But you could compare them as well as I could.
I didn't copy|paste until this morning. I merely changed the part I
originally asked you about ($A" & NewRow2...). I went back and copy|pasted
like you suggested and sure enough it works. I went back and tried to find
the descrepencey but couldn't. Your use of the & makes the code look better
anyway. Just out of curiosity what else did you change?

Dave Peterson said:
Did you retype my suggestion or did you copy|paste?

I'm betting that you changed something else.


When I use ($A" & NewRow2... like you suggest I get an error [end of
statement expected]. I even tried ("$A" & NewRow2... I also tried (""$A"" &
NewRow2... which did put the formula in the spreadsheet but it printed like
this ($A & NewRow2... so the code didn't interpret the variable, it just
printed the variable name. What am I doing wrong?

:

I didn't notice that you were putting the formula in a single cell.

You can use:
..range("z" & newrow2).formula = _
or
..cells(newrow2,"Z").formula = _
instead of:
..Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

It makes it a little easier to read.

NewRow2 = 6
With Worksheets("Sheet1")
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),11,FALSE))"

.Range("AA" & NewRow2).Formula = _
"=IF(ISNA(VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE)),""""," _
& "VLOOKUP($A" & NewRow2 _
& ",INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'" _
& "!$F$1:$P$5000""),6,FALSE))"

.Range("Z" & NewRow2 & ":AA" & NewRow2 + 4).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount
End With

And I didn't see a response to the other thread (yet??), but remember when you
use =indirect(), the sending workbook has to be open.

Bishop wrote:

Here's my code snippet:

NewRow2 = 6
For RowCount = Start To NewRow
If NewRow2 < NewRow Then
.Range("Z" & NewRow2 & ":Z" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),11,FALSE))"
.Range("AA" & NewRow2 & ":AA" & NewRow2).Formula = _

"=IF(ISNA(VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE)),""""," & _

"VLOOKUP($A6,INDIRECT(""'[""&$Y$2&""]By_Rep_by_Filter'!$F$1:$P$5000""),6,FALSE))"
.Range("Z" & NewRow2 & ":AA" & (NewRow2 + 4)).FillDown
NewRow2 = NewRow2 + 8
End If
Next RowCount

The first run through this code puts the formulas in Z6 and AA6 and fills
down to Z10 and AA10. Which is what I want. After "NewRow2 = NewRow2 + 8"
NewRow2 is now 14. Again, this is correct. The problem is I have $A6 in my
VLOOKUP formula so, on the second pass, the formulas are place in Z14 and
AA14, as they should, but my VLOOKUP formula still has $A6 and I need it to
have $A14. How do I make that change like it does in my .Range?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

vlookup and using single quote 0
Calculating(Processors(2)): % 1
Calculating(Processor(2)): % 8
Vlookup 6
Defining a dynamic table_array for vlookup 9
Vlookup for value 1
Vlookup error 1
Array with many cells from a row but not in order or together 11

Top