# 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.

## Files

• 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.

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.

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.

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!