[RESOLVED] [Excel] VLOOKUP

I need to programmatically set up a vlookup for a column starting at row 11 column G. The lookup range will be on sheet2 row 2-65535 and columns A:B. The issue is that it needs to be dynamic as the lookup range will be increased as new items are added when new vendors are added.

This is a smaller part of a large solution and once the lookup is run I need column G to be converted to a non-lookup column or just a Values column. This sheet is being read in by another program and the formula is throwing it off.

I will be calling the code from a menu item so that part I can do. I am on a time limit here or I would be able to do more of it myself.

So how to modify the lookup source range to include all items when a new one is added?

Thanks guys

VB Code:

'Select column G row 11 - End
    Workbooks(1).Sheets("Sheet1").Range("G11:G65535").Select
    'Apply formula for vlookup - this already does rows 2 - 13 and columns 1 - 2 on sheet2. needs extending
    Workbooks(1).Sheets("Sheet1").Range.FormulaR1C1 = "=VLOOKUP(RC[11]:R[11]C[11],Sheet2!R[-9]C[-6]:R[2]C[-5],2,TRUE)"

Something along these lines?

VB Code:

'Select column G row 11 - End
    Workbooks(1).Sheets("Sheet1").Range("G11:G65535").Select
    'Apply formula for vlookup - this already does rows 2 - 13 and columns 1 - 2 on sheet2. needs extending
    doodah = Workbooks(1).Sheets("Sheet2").Range("B:B").SpecialCells(xlCellTypeLastCell).Row
    Workbooks(1).Sheets("Sheet1").Range.FormulaR1C1 = "=VLOOKUP(RC[11]:R[11]C[11],Sheet2!R[-9]C[-6]:R[" & doodah & "]C[-5],2,TRUE)"

zaza

What does the "R[-9]C[-6]" part translate to? I thought it would be positive #s?

Does that not stay as R[-9]C[-6] because you're going from cell G11, hence row 11 -> 2 and column 7 -> 1, i.e. start point A2?
I'm presuming that new vendors will be added in columns A and B on the first available row, so if the current code works for the first 12 then all that needs to be done is to extend that range to the last filled cell.

zaza

Yes, but the G destination column is on sheet1 and the lookup table is on sheet2. So do they mean it navigates from sheet1 to 2 and then moves up -2 and left -9?

I believe so, but you could always just put in R2C1, because the startpoint of your range is not dynamic. Similarly you could omit the square brackets in the second part and just put "...R" & doodah & "C2..."

i.e.

VB Code:

Workbooks(1).Sheets("Sheet1").Range.FormulaR1C1 = "=VLOOKUP(RC[11]:R[11]C[11],Sheet2!R2C1:R" & doodah & "C2,2,TRUE)"

I'm not sure what you're actually supposed to be looking up with that RC[11]:R[11]C[11] bit, but presumably that's correct.

Ah Thanks as that stupid -#s were throwing me off. Now I see the light. Plus not getting enough sleep from trying to make a deadline for a site I am making is not helping.

:thumb:

Next part is to convert the G column to Values instead of vlookup formulas.

I guess I can record a macro for that and tweak it unless you know of a trimmed down way? :D

Cant you tell I am stressing over the deadline lol

The quick and dirty way...

VB Code:

Range("G11:G65535").Select
    Selection.Copy
    Range("G11:G65535").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

zaza

Thanks, so it copies itself and pastes back in as values. Cool!

The stress level has just come down a bit lol. :thumb:

Anything else I can help you with? If not, don't forget to mark this thread as resolved... :p


jk. Always a pleasure, RD.

Unles you know sp's or asp.net or ado.net? :D

ADO.Net I've never worked with. ASP.Net I'd like to learn. And I don't even know what sp's stands for. So that's no, no and no. Sorry. :D

Stored Procedure in SQL Server. :)

I just got some help with the security of the sp so I am now moving on to the ADO.NET part. Thanks anyways :thumb:

More destressing going on. 3-4 hours left. :(