Programming VLOOKUP in VBA

    Hi & Hello


    I'm in the process of writng my first VBA program for Excel. :yikes:


    I have three worksheets, the first is the calculation sheet, the second two are reference sheets which are currently referenced using the VLOOKUP command. Due to the nature of the calculations, the ranges being referenced vary from calculation to calculation, and are very rarely adjacent columns.


    Examples are:


    =VLOOKUP(A2,'Part Look-up'!$A$2:$Z$95,26,TRUE)


    =VLOOKUP(I2,'Build Look-up'!$B$2:$C$26,2)


    How would these be programed into VBA?


    [If you need more info then please ask, having not used one of these firmuns before I'm unsure how much data is required....]

    Hi Gethzerion!


    I am sure that the guys out there adding their knowledge to the Forum are more than willing to help with the code. I can only help you with code once I've used the Macro Recorder in Excel to do the actual programming for me.


    That is, unless the previously mentioned guys didn't have to help me anyway!


    Have you tried the Macro Recorder yet?


    Simply click Tools>Macro>Record Macro and watch what happens. As a word of advice, have only the one workbook open initially, as it makes things easier until you get the hang of it. Name the Macro (or keep it what is displayed) and click on OK. A "button"-like "thing" appears with which you can eventually STOP the macro recording. If not, Stop the recording by going to Tools>Macro>Stop Recording.


    Now do in Excel what you want the VBA recorder to record. Type the VLOOKUP functions in the required cells, determine the ranges by selecting the sheets etc. and click on STOP recording when you are done.


    Go to Tools>Macro>Visual Basic Editor.


    Click Tools>Macros and highlight the recorded macro. Click Edit to open it and have it reveal the language you are looking for!


    You can have a button on the sheet to trigger the calculations, or some other thing to trigger the calculations.


    I did not give you the code (I am not that clever), but maybe the "teaching to fish" instead of the fish will mean as much to you as it does to me!


    Kindest regards


    Harry

    Hi,


    do you want Vba to create the formules in the worksheet?(see solution Harry) Or do you want the same result with Vba without Vlookup-formule?


    Gollem

    Thanks for the greetings :)


    Harry: I've used the trick you suggested on several occasions to learn how to select columns, put borders on cells etc. I have to say it works very well :)


    Gollem: I could use the code produced by using the method proposed by Harry - it produces a result:


    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet2!R[-9]C[-4]:R[25]C[3],4,TRUE)"


    However the range of the lookup table Sheet2!R[-9]C[-4]:R[25]C[3] appears to directly refer to the active cell in sheet1. Given that I have 1000+ lines on sheet 1 I cannot keep changing the range of the lookup table. Can I fix this range somehow?


    What I would prefer would be:
    ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],Sheet2!$A$2:$Z$95,26,TRUE)"


    Just to complicate matters, some of the vlookup functions used are hidden inside if ... then statements, for example:


    =IF(K252="All",VLOOKUP(I252,'Build Look-up'!$B$2:$F$26,3),IF(K252="SWB",VLOOKUP(I252,'Build Look-up'!$B$2:$F$26,4),VLOOKUP(I252,'Build Look-up'!$B$2:$F$26,5)))


    Using the above method I may have to spread this calculation across several columns?


    I guess part of the reason of moving over to VBA, apart from the fact it makes life a lot easier on several levels was to make the spreadsheet a lot more elegent and easier to modify in the future if necessary.


    Any help would be appreciatted

    Hi again! (And welcome to the board, I might add)


    From what I see, it would seem as if you are using R1C1 Reference Style in your workbook. Is this the case?


    I have not done what I proposed you do with your problem (Using the Recorder) and will only be able to get to that later tonight.


    However, if you are using R1C1 Reference Style, can you change that and re-run the Recorder? (By the way, I am acting as though I know something, which I do not. Someone may hopefully come to your rescue before tonight and I can learn something!)


    Kindest regards


    Harry

    Hi Harry


    Just to confirm the R1C1 Reference style is not checked in the Options settings. I have also tried locking the table array using the '$' signs with no luck. :sniff:


    Guess it may be back to the drawing board :sigh:

    OK I could be a prawn on this one, but can I just confirm what I think I am seeing:


    In RC notation:


    if either/both the 'R' and 'C' are followed by square brackets, this refers to a move from the current active cell?


    If they are simply followed by a number, this refers to the absolute cell address - that is R1C1 == A1?

    correct.


    R1C1=$A$1
    another method is to name the range like
    Sheets("SheetName").Range("A1:Z1000").name = "List"


    then you can use it in the formula. like


    "=VLOOKUP(RC[-4],List,26,TRUE)"

    Hi Gethzerion!


    Could not walk away just yet!


    I noticed that the Recorder automatically uses the R1C1 style.


    I changed the formula to look like the stuff I recognise in Excel and it worked when I ran the macro. (My example: ActiveCell.Formula = _
    "=VLOOKUP(Sheet1!F1,Sheet2!$D$8:$G$13,4,FALSE)"


    However, you'll notice that it simply refers to the ActiveCell, meaning that wherever the active cell is, the formula will be written when the macro is run.


    In order to get it to do the calculations for the required cell, the cell has to be made the active cell. I have not spent too much on this, but you will probably have to add code such as:


    Range("A1").Select 'making it the ActiveCell


    BEFORE the formula in order to sort that out.


    Running ALL of the functions one after the other will possibly mean a loop thing (Which someone else will have to help) or you'll have to go:


    Range("A1").Select
    ActiveCell.Formula etc
    Range("A2").Select
    New Formula
    etc.
    etc.
    until you've gone through all 1000 lines!


    Boy!


    Depending on the actual functions, a loop that automaticall does and re-does the function in different cells may work, don't you think?


    I'll keep in touch!


    (I like your expression about the PRAWN!)


    Kindest possible regards


    Harry

    Thanks for all the help folks - that would seem to fit the bill :)


    I've already got the loop "thingy" working for the spreadsheet to work through all the lines of spreadsheet. I just couldn't get my head around how do a vlookup or equivalent in VBA


    Anyhow thanks again :)

    if you want to apply a formula to a range using VBA code, then you can also do the following:


    Assuming you have data in columns A-C, and you want to put the formula in column D


    Range("D1",Range("C65536").End(xlUP).Offset(0,1)).formulaR1C1="your formula here"


    This will put your formula into every cell in your used range in column D


    This avoids having any loop.


    James

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login