VLOOKUP formula with subtotals

  • Hi,


    I used this code to automate VLOOKUP formula in my Excel file, but I must "pick up" subtotal values in VLOOKUP. How to do that.


    This is code:



    I tried something like this:


    Code
    Sheet3.Cells(Izlaz_Row, Izlaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3]&" Total",Izlaz!R2C1:R10000C4, 2, False)"


    but it does not work. Can anybody help me?


    Thanks.

  • Re: VLOOKUP formula with subtotals


    Welcome to the forum...


    Not sure what you are trying to do, but at the very least, you have a syntax error here:


    Code
    Sheet3.Cells(Izlaz_Row, Izlaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3]&" Total",Izlaz!R2C1:R10000C4, 2, False)"


    Should probably be...


    Code
    Sheet3.Cells(Ulaz_Row, Ulaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-3]" & Total & ",Ulaz!R2C1:R10000C4, 2, False)"

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: VLOOKUP formula with subtotals


    Thanks for helping.


    It was syntax error, but I want to LOOKUP only subtotal values from Ulaz and Izlaz sheet im my VLOOKUP formula (something like "2533144-BLK Count = 4" or "2533144-BLK Total = 40" like in sheets "Ulaz" and "Izlaz" in the file). That values I should get in C10, and D10 cells in the sheet "PocSta".


    When I use formula in Excel sheet it is:


    Code
    =VLOOKUP (A2&" Total", Izlaz!A2:D10000, 2, false)


    It works as I want. Can You help me with this?





  • Re: VLOOKUP formula with subtotals


    OK, so it seems you know the formula are just havig difficulty converting to VBA?


    =VLOOKUP (A2&" Total", Izlaz!A2:D10000, 2, False)


    something this:

    Code
    your_range.FormulaR1C1 = "=VLOOKUP(RC[-3]" & " Total" & ",Izlaz!R2C1:R10000C4, 2, False)"


    (untested)

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: VLOOKUP formula with subtotals


    Thanks for helping. This is almost it. With this I have "A2 Total" in formula, but I need "A2&" Total". How can I get this?


    Thanks again.


    P.S. I tried with this:


    Code
    Sheet3.Cells(Izlaz_Row, Izlaz_Clm).FormulaR1C1 = "=VLOOKUP(RC[-2]" & "&" & """ & " Total" & """ & ",Izlaz!R2C1:R10000C4, 2, False)"


    But something is wrong.




  • Re: VLOOKUP formula with subtotals


    Oh, I got you... sorry, I misunderstood what you were trying to do. This seems to work (in your sample), for both the count and total vlookup:



    So you were on the right track with using """


    Personally, I typically dont use """ as it is visually hard to read and debug and instsead use CHR(34), which evaluates to a "


    So its really just about building up the string the match the formula - it can often get very tricky, but well done on trying anyway.


    Regards,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: VLOOKUP formula with subtotals


    It works now. Thanks a lot for helping.


Participate now!

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