VBA- Vlookup R1C1 breakdowns
May 30, 2020 5:26 AM Subscribe
Hello,
As part of my job, I often have to clean up others' VBA code. looking for a little help understanding a specific line of code. Details inside.
Never having been formally trained, my method is usually taking someone else's workbook (when they have left the organization, and haven't already commented their code themselves) and commenting out every line until I understand the VBA in and out. It usually works and is very helpful to me.
I've determined that the person that "wrote" this macro did it by recording themselves through the macro recorder. The vlookups came out as being in FormulaR1C1.
I'm looking for someone to help me break down what the R1C1's are actually doing, because I've managed to get myself confused, and I think these are the key to understanding what I need to 'fix' next.
Any help is welcome!
Here's what I would like explained. Keep in mind, I understand vlookup well, just not the R1C1 in the first portion of the vlookup joined by an &.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-20]&RC[-19],'Rate Vlookup'!R2C3:R" & lastrow & "C36,3,FALSE)"
Never having been formally trained, my method is usually taking someone else's workbook (when they have left the organization, and haven't already commented their code themselves) and commenting out every line until I understand the VBA in and out. It usually works and is very helpful to me.
I've determined that the person that "wrote" this macro did it by recording themselves through the macro recorder. The vlookups came out as being in FormulaR1C1.
I'm looking for someone to help me break down what the R1C1's are actually doing, because I've managed to get myself confused, and I think these are the key to understanding what I need to 'fix' next.
Any help is welcome!
Here's what I would like explained. Keep in mind, I understand vlookup well, just not the R1C1 in the first portion of the vlookup joined by an &.
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-20]&RC[-19],'Rate Vlookup'!R2C3:R" & lastrow & "C36,3,FALSE)"
Response by poster: @nickggully Thank you so much for your answer. I think I didn't ask correctly what I was looking for, which is what I was afraid of! Curse of the question asker, I think.
I'm mostly having difficulty with this portion:
=VLOOKUP(RC[-20]&RC[-19]
What is the ampersand doing there? I can't figure out what an RC[-20]&RC[-19] is joining.
Thank you so much!
posted by I_count_crows at 6:22 AM on May 30, 2020
I'm mostly having difficulty with this portion:
=VLOOKUP(RC[-20]&RC[-19]
What is the ampersand doing there? I can't figure out what an RC[-20]&RC[-19] is joining.
Thank you so much!
posted by I_count_crows at 6:22 AM on May 30, 2020
Best answer: Ampersand is concatenation.
I think RC[-20] is equivalent to R[0]C[-20].
So this is taking whatever is -20 columns away and concatenating it to what is -19 columns away, and making that what is searched for.
so if they had "cat" and "food", lookup would be catfood
posted by nickggully at 6:52 AM on May 30, 2020 [5 favorites]
I think RC[-20] is equivalent to R[0]C[-20].
So this is taking whatever is -20 columns away and concatenating it to what is -19 columns away, and making that what is searched for.
so if they had "cat" and "food", lookup would be catfood
posted by nickggully at 6:52 AM on May 30, 2020 [5 favorites]
Response by poster: That did it! You helped knock loose the rusty cogs in my head. That's exactly what they were doing!
posted by I_count_crows at 8:00 AM on May 30, 2020
posted by I_count_crows at 8:00 AM on May 30, 2020
This thread is closed to new comments.
So if lastrow=4, then that parameter becomes 'Rate Vlookup'!R2C3:R4C36
posted by nickggully at 6:08 AM on May 30, 2020