Macro fills in blank cells with formula stops at endrow

[vanessaaleks]

Hi All,
I am creating a macro to do a vlookup of credit card charges against a traveler report.The spreadsheet I am working with has a changing amount of rows week to week, since the number of trips booked are different all the time. I also have a varying amount of data, and will inevitably have blank information in column E (header is passenger name). My macro sorts the information so that the traveler names that are already pre-filled are at the top. Now I want the macro to continue, but am stumped on how to do the next step. Here's what I want it to do:

Find the first blank cell in column E and enter the formula
=if($C66="TPT",vlookup($D66,Cognos!$B:$E,4,false),vlookup(CyberArc!$D66,Cog nos!$D:$F,3,false))
Then I want the formula to be dragged down through to the last row (based on an endrow value from a count of column A)

Also, I'm curious - if it wasn't already sorted so that the blank rows are all together, would there be a way to do this so it would fill in blank cells using the row number the blank cell is in?

Thank you for your help and time.

[stanleydgrom]

vanessaaleks,

See the attached workbook "Macro fills in blank cells with formula stops at endrow - vanessaaleks.xls".

Run the "Test" macro. If a box opens that looks like a file save as box, then just press the "ESC" key.

Then check the formula in cell E21, to see if it is correct.


Have a great day,
Stan

[vanessaaleks]

Hi Stan,
When I did the test macro the formula in E21 was:
=IF($C21="TPT",VLOOKUP($D21,Cognos!$B:$E,4,FALSE),VLOOKUP(CyberArc!$D30,Cog nos!$D:$F,3,FALSE))

This is close to what I am looking for, except for the second VLOOKUP should have also been row 21. Is the [9] adding 9 rows down to make it 30? I am fairly new to formulas/excel, so am trying to soak up the how of everything. If you have time to explain what it is this entry is doing I would be so appreciative.

I'm excited to learn the endrow option you provided for the range. I knew how to count A to get endrow, but it was the second part that I was struggling with. I didn't realize you could name a second count like that.

When looking at what you are doing, is the RC4 a column count of 4 columns over?


[VBA]Sub Test()
Dim endrow, endrowE As Long
'endrow value from a count of column A
endrow = Cells(Rows.Count, "A").End(xlUp).Row
endrowE = Cells(Rows.Count, "E").End(xlUp).Row
With Range("E" & endrowE + 1 & ":E" & endrow)
'E
'=IF($C21="TPT",VLOOKUP($D21,Cognos!$B:$E,4,FALSE),VLOOKUP(CyberArc!$D30,Co gnos!$D:$F,3,FALSE))
'=IF(RC3="TPT",VLOOKUP(RC4,Cognos!C2:C5,4,FALSE),VLOOKUP(CyberArc!R[9]C4,Cognos!C4:C6,3,FALSE))
.FormulaR1C1 = "=IF(RC3=""TPT"",VLOOKUP(RC4,Cognos!C2:C5,4,FALSE),VLOOKUP(CyberArc!R[9]C4,Cognos!C4:C6,3,FALSE))"

End With
End Sub[/VBA]

[vanessaaleks]

Aha! After looking at it longer I figured out what it is you did. I have never used RC but it makes sense to do this to specify the Column vs. specifying the specific row in the formula. I learned so many tricks from this! Thank you again!

[stanleydgrom]

vanessaaleks,

See the NEW attached workbook "Macro fills in blank cells with formula stops at endrow - vanessaaleks.xls".

Run the "Test" macro. If a box opens that looks like a file save as box, then just press the "ESC" key.

Then check the NEW formula in cell E21, to see if it is correct.

Is the [9] adding 9 rows down to make it 30? I am fairly new to formulas/excel, so am trying to soak up the how of everything. If you have time to explain what it is this entry is doing I would be so appreciative.

My original formula was wrong. The "[9]" in R1C1 is an offset from the row the formula is being entered into.

is the RC4 a column count of 4 columns over

The "R" is the row the formula is in; the "C4" is column 4 = column "D".

It took me a long time to realize that when copying a formula to a range it was easier to use R1C1, instead of copying the formula to a cell, and then copying that cell down.


Have a great day,
Stan