Most effective way to insert vlookup formula?

srosk

Board Regular
Joined
Sep 17, 2018
Messages
132
I have a spreadsheet that I will be manually adding data to columns C:K. Periodically, I would like to run a macro that searches for rows where column C has a value, but column B is blank for that row. If column B is blank:

insert a separate formula in column A and column B for that specific row

Is this possible with VBA?
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
What would be the formula for the first blank cell in column B and the formula for the cell in column A in the same row if you done it manually? and what is the cell reference for the first blank cell in column B?
 
Upvote 0
Could you give an example of the formula that goes in A and the formula that goes in B, or what do you expect in A and B ?
 
Upvote 0
Sure, sorry.. simple Vlookup :)

Column A:
Code:
=VLOOKUP(C4,'A B'!B:D,2,FALSE)

Column B:
Code:
=VLOOKUP(C4,'A B'!B:D,3,FALSE)
 
Upvote 0
And the cell reference....

and what is the cell reference for the first blank cell in column B?

Edit: assuming the cell is B4 and the cells are truly Blank then maybe

Code:
Sub InsertFormula()
    With Columns("B").SpecialCells(4)
        .Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,FALSE)"
        .FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,FALSE)"
    End With
End Sub
 
Last edited:
Upvote 0
That's what hopefully.. the script can find :).. My vision, is that for each row that has a value in column C, script checks if column A&B are blank.. if blank, insert formula.. if not blank.. ignore. Once finish row 2, look at row 3, etc. Because it will vary as data is loaded, if that makes sense.
 
Upvote 0
That's what hopefully.. the script can find :).. My vision, is that for each row that has a value in column C, script checks if column A&B are blank.. if blank, insert formula.. if not blank.. ignore. Once finish row 2, look at row 3, etc. Because it will vary as data is loaded, if that makes sense.

It will find it but we need to know what the cell is currently as we need to know what the formula is relevant to.
 
Upvote 0
I am not so sure I understand, so pardon my ignorance. If column C contains a value in any row (value could be any alphanumeric value)... then if column A is blank, insert column A formula for that row... and if column B is blank, insert column B formula for that row that was checked in column C.

Is this any bit helpful?

Thanks for all that you do!
 
Upvote 0
Try this

Code:
Sub test_formula()
    Dim c As Range, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For Each c In Range("B5:B" & lr).SpecialCells(xlCellTypeBlanks)
        If c.Offset(0, 1) <> "" Then
            c.Offset(0, -1).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,0)"
            c.FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,0)"
        End If
    Next
End Sub
 
Upvote 0
First of all in post number one you only stated if column B is blank insert the formulas in column A & B. now it is treat the columns separately for blanks, it isn't an issue but which is it?

Second can you please just answer the question asked, as you look at it now what cells would the formulas you posted go in (just be assured if the formula went in B6 rather than B4 it would make a difference)

If for example it was A4 and B4 the code would be

Code:
Sub InsertFormula2()
        Columns("A").SpecialCells(4).FormulaR1C1 = "=VLOOKUP(RC[2],'A B'!C[1]:C[3],2,FALSE)"
        Columns("B").SpecialCells(4).FormulaR1C1 = "=VLOOKUP(RC[1],'A B'!C:C[2],3,FALSE)"
End Sub

for separate columns (no need to loop through the cells).
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top