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?
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?
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?
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
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 ?
Regards Dante Amor
Sure, sorry.. simple Vlookup
Column A:
Column B:Code:=VLOOKUP(C4,'A B'!B:D,2,FALSE)
Code:=VLOOKUP(C4,'A B'!B:D,3,FALSE)
And the cell reference....
Edit: assuming the cell is B4 and the cells are truly Blank then maybeand what is the cell reference for the first blank cell in column B?
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 by MARK858; May 2nd, 2019 at 06:26 PM.
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
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.
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
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!
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
Regards Dante Amor
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
for separate columns (no need to loop through the cells).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
Last edited by MARK858; May 2nd, 2019 at 06:47 PM.
Test VBA on a copy of your data (remember you can't normally reverse the action)
Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]
To post a screenshot try one of these links
MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste
Like this thread? Share it with others