PDA

View Full Version : VLOOKUP but the cell will be different everyday



davmec93
10-21-2010, 01:26 PM
Hi All! I have a question. In my spreadsheet I am using VLOOKUP to get the qty of production from another sheet. The issue I'm having is that today the cell in my formula is in column D. Tomorrow it will be AD and so on. My question is how can I put that into my VLOOKUP? I thought about using an xlToRight and then offset a -2 but I'm not sure how to add that to the VLOOKUP. Here is what I'm currently using...it worked today but it won't tomorrow.
For Counter = 1 To 6
Set curCell = Worksheets("ProjectedCompletion").Cells(Counter, 10)
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Select
Next Counter

CharlesH
10-21-2010, 02:15 PM
hi,

Check this thread it may help you develope a code for what you want
http://www.vbaexpress.com/forum/showthread.php?t=34524
It was one that I worked on with the help of Simon.

mdmackillop
10-21-2010, 02:15 PM
Change the data to absolute references or use range names

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"

davmec93
10-21-2010, 02:20 PM
hi,

Check this thread it may help you develope a code for what you want
http://www.vbaexpress.com/forum/showthread.php?t=34524
It was one that I worked on with the help of Simon.

Hey, thanks for the reply...I will look at it straight away!

CharlesH
10-21-2010, 02:28 PM
Check mdmackillop suggestion.
I added the xlleft to the code, however I did not test.



Sheets(ws1).Cells(2, 9).Formula = "=VLookup(G2,'" & Sheets(ws1).Range("zz2").End(xlToLeft).Column & "'!G$2:G$" & s2lrow & ", 1," & "False" & ")"

davmec93
10-21-2010, 02:41 PM
Change the data to absolute references or use range names

ActiveCell.FormulaR1C1 = "=VLOOKUP(RC4,'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
Please excuse my ignorance but I'm not sure what this formula is supposed to do. RC4...That does not seem to compute in my spreadsheet. Sorry...

mdmackillop
10-21-2010, 02:54 PM
The code I posted is just an example. It's not clear from your question how the formula and data positions are related. How does it change from day to day? A posted example would assist.

davmec93
10-21-2010, 02:54 PM
Check mdmackillop suggestion.
I added the xlleft to the code, however I did not test.



Sheets(ws1).Cells(2, 9).Formula = "=VLookup(G2,'" & Sheets(ws1).Range("zz2").End(xlToLeft).Column & "'!G$2:G$" & s2lrow & ", 1," & "False" & ")"


WOW...I'm really feeling inadequete right now. I'm afraid that I don't know how to incorporate this code. I'm not defining something correctly I think.

davmec93
10-21-2010, 03:08 PM
The code I posted is just an example. It's not clear from your question how the formula and data positions are related. How does it change from day to day? A posted example would assist.

Well today the data that I want the VLOOKUP to return is Column Z
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C24,24,FALSE)"
tomorrow I will add more data and the column I want return will be AA.ActiveCell.FormulaR1C1 = "=VLOOKUP(R[0]C[-9],'Summary'!R2C1:'Summary'!R580C26,26,FALSE)"
I'm just not sure how to code that.

mdmackillop
10-21-2010, 03:13 PM
Where is the activecell and the lookup value? Do these change? Can you post a workbook showing a sample layout? It makes the solution easier.

davmec93
10-26-2010, 11:09 AM
Where is the activecell and the lookup value? Do these change? Can you post a workbook showing a sample layout? It makes the solution easier.

Here is a sample. The vlookup is on the "Sheet2" tab in column J. It looks up the item number in column A on the Summary sheet and returns the value in the "Qty" column. Today to the Qty value is in coumn F. Tomorrow I will insert more data and the value I need to return will be in column H. I'm sorry if I'm not expaining very well and I really appreciate your attempt to help me.

davmec93
10-26-2010, 11:14 AM
Here is a sample. The vlookup is on the "Sheet2" tab in column J. It looks up the item number in column A on the Summary sheet and returns the value in the "Qty" column. Today to the Qty value is in coumn F. Tomorrow I will insert more data and the value I need to return will be in column H. I'm sorry if I'm not expaining very well and I really appreciate your attempt to help me.

Please try this sample instead....sorry

jdub12280
10-26-2010, 01:42 PM
I'm no expert, however, I would suggest using the recorder to write the formula for you. If I am understanding what you are trying to do correctly, I would use a combination of vlookup + match. If your data shifts to the right constantly, match will return the correct column ( assuming your columns are labeled ). I use vlookup / match all the time so i don't have figure out the location of the data im looking for.

My two cents...

-Justin

davmec93
10-26-2010, 02:12 PM
I'm no expert, however, I would suggest using the recorder to write the formula for you. If I am understanding what you are trying to do correctly, I would use a combination of vlookup + match. If your data shifts to the right constantly, match will return the correct column ( assuming your columns are labeled ). I use vlookup / match all the time so i don't have figure out the location of the data im looking for.

My two cents...

-Justin

I'm not sure the recorder will help me on this one but I do appreciate your input! :friends: