| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reference Cell in VLOOKUP
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" wrote: 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? -- Dave Peterson |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Cell in VLOOKUP
Did you retype my suggestion or did you copy|paste?
I'm betting that you changed something else. Bishop wrote: 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" wrote: 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? -- Dave Peterson -- Dave Peterson |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Cell in VLOOKUP
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" wrote: Did you retype my suggestion or did you copy|paste? I'm betting that you changed something else. Bishop wrote: 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" wrote: 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? -- Dave Peterson -- Dave Peterson |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Reference Cell in VLOOKUP
I don't recall.
But you could compare them as well as I could. Bishop wrote: 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" wrote: Did you retype my suggestion or did you copy|paste? I'm betting that you changed something else. Bishop wrote: 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" wrote: 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? -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Vlookup cell reference | Excel Discussion (Misc queries) | |||
| vlookup cell reference | Excel Discussion (Misc queries) | |||
| VLookup and a Cell Reference | Excel Worksheet Functions | |||
| VLOOKUP using cell reference | Excel Worksheet Functions | |||
| reference vlookup cell | Excel Programming | |||
Hybrid Mode
