Paste formula in empty cells next to a variable range

davidmg1982

Board Regular
Joined
Oct 12, 2015
Messages
64
Im looking for a vba solution to paste a formula on the empty cells in Column D next to a variable range in column A, any help will be very appreciated.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
What is the variable range in A & what is the formula?
 
Upvote 0
What is the variable range in A & what is the formula?
Thanks Fluff, i might have used the wrong words, by variable range I meant total rows in column A will be changing every day.

The formula is:
"=IFERROR(HLOOKUP(RC[-3],DataForm!R3C3:R12C3,9,0),"""")"
 
Upvote 0
Ok, do you want that formula in all col D down to the last row in col A, or just from the last used cell in D down to the last cell in col A, or something else?
 
Upvote 0
Ok, do you want that formula in all col D down to the last row in col A, or just from the last used cell in D down to the last cell in col A, or something else?

-The formula must be inserted in all blank cells in column D down to the last row in Column A, examples:

Example 1
Colums AColumn D
P12345Paste formula here
P12344Existent value
P15677Paste formula here
P12456Paste formula here

<tbody>
</tbody>

Example 2
Column AColumn D
P12985Existent value
P12644Existent value
P15932Paste formula here
P12546Existent value

<tbody>
</tbody>
 
Upvote 0
Ok, how about
Code:
Sub davidmg()
   With Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=IFERROR(HLOOKUP(RC[-3],pcode!R3C3:R12C3,9,0),"""")"
   End With
End Sub
 
Upvote 0
Ok, how about
Code:
Sub davidmg()
   With Range("D1:D" & Range("A" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlBlanks).FormulaR1C1 = "=IFERROR(HLOOKUP(RC[-3],pcode!R3C3:R12C3,9,0),"""")"
   End With
End Sub

Exactly what I need it, thank you for your time.
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

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