Macro to fill cells with formulas, text and blanks

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
87
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

paldob

New Member
Joined
Apr 23, 2018
Messages
28
Could you possibly explain a little more what you are wanting? Have you tried =IFERROR(VLOOKUP($C13,$AT$13:$BA$13,D$8,FALSE),"")
 
Last edited:

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
87
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

New Member
Joined
Apr 23, 2018
Messages
28
Code:
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

Board Regular
Joined
Sep 30, 2017
Messages
87
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

New Member
Joined
Apr 23, 2018
Messages
28
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))"


 
Last edited:

muhleebbin

Board Regular
Joined
Sep 30, 2017
Messages
87
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web

ADVERTISEMENT

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

paldob

New Member
Joined
Apr 23, 2018
Messages
28
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

Board Regular
Joined
Sep 30, 2017
Messages
87
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2010
Platform
  1. Windows
  2. MacOS
  3. Mobile
  4. Web
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

New Member
Joined
Apr 23, 2018
Messages
28
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,790
Messages
5,524,893
Members
409,609
Latest member
Channingz

This Week's Hot Topics

Top