Macro to fill cells with formulas, text and blanks

[muhleebbin]

Thanks in advance for your assistance with this!

Is it possible to create a macro that fills cells with formulas, text and/or blanks depending on the cells in column C?

My workbook is currently structured with a master list of accounts in Sheet2('Variables') and Sheet1('Report'). On Sheet2, column a is the master list and columns b thru j are the potential options to fill from. Most are formulas, some are texts and a few i'd like to leave blank.


This is currently what i'm working with but as you can tell going through 349 rows of this would make this quite tedious and annoying

Worksheets("Sheet1").Range("D13").Formula = "=VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE)"

The blank cells is what's currently really throwing me in for a loop

 

[paldob]

Could you possibly explain a little more what you are wanting? Have you tried =IFERROR(VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE),"")

 

[muhleebbin]

Basically i'm trying to create a macro that resets my Report worksheet. If I did it line by line, i'd have 2,443 lines (349 rows x 7 columns) i'd have to input and if I add a new row to the Report worksheet i'd have to fix every subsequent line in the macro to accommodate the new row.

I guess i'm looking for a vlookup macro that will fill the cell with it's appropriate formula, text or blank cell.

 

[paldob]

Dim Lastrow As Integer
   Lastrow = Application.Inputbox ("Enter the last row number for your formula")

Worksheets("Sheet1").Range("D13:D" & Lastrow).Formula = "=VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE)"

That way you should only need 7 lines, one for each column?
​

 

[muhleebbin]

That worked for the formulas and text however it still leaves a "-" for the blank cells. Is it possible to clear the contents from the blank cells but still leave the formulas? In this reset some of the sum formulas will have 0 values that would also be cleared out

 

[paldob]

Can't test this as I'm currently out for a meal, but could you try the formula -

"=If(VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE)=""-"","""",VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE)"

or this one

"=If($C13="""","""",VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE))" ​

​

 

[muhleebbin]

I keep getting an error when I put formulas in with multiple "

 

[paldob]

Make sure the close bracket is doubled up on my first formula )) I closed the second vlookup but didn't notice that I never closed the IF statement - this would cause an error when running through VBA.

You need the 4 x "" to search for a blank within the formula. This is because when you put " it ends the text. Therefore using """" the First " is ending then 2 middles "" are the Search and the fourth " is starting the next part of the formula.

 

[muhleebbin]

I definitely was missing the final ) and that threw it off, thanks! However i'm still getting the formula input for the blank. When I go to the second line in column d (which should be left blank) the cell is filled with the formula.

Worksheets("Sheet1").Range("D13:D" & Lastrow).Formula = "=IF(VLOOKUP($C13,Sheet2!$S$1:$Z$500,Sheet1!D$8,FALSE)=""-"","""",VLOOKUP($C13,Sheet2!$S$1:$Z$500,Sheet1!D$8,FALSE))"

Moved the table array and this is now the line of code but everything looks the same, is there anything I missed?

 

[paldob]

Can you try a for loop?

Dim Rng as range, Cel as range
Set Rng = Worksheets("Sheet1").Range("D13:D" & Lastrow)

For each cel in range

If not cel = “” then
Enter formula here
End if

Next

This should skip the blanks. May need to tweak the formula a bit as it may become static