VBA Copy/Paste formula from D1 into empty cells in column D, starting at D6 and going down to last row

[deniseturan]

​

Comment: added excel tag [h=2]Copy/Paste formula from D1 into empty cells in column D, starting at D6 and going down to last row[/h] I need to copy and paste formula from D1 in "010 - RPL" into all empty cells in column D, starting with D6, and down to last row. I have been trying various approaches the last couple of days and found this to be the closest, but it doesn't mean anything to me as long as it is removing my headers that are used for pivot tables. This is the closest I have got, but it is overiding all of the headers I have throughout the data. The good thing is that it is taking my Vlookup formula in D1, "=VLOOKUP(C1,'Departments Lookup'!$B:$D,3,FALSE)" and looking into the correct cell address. Example would be: Starting in D6, this would be doing the vlookup in C6. <code style="background-color: rgb(239, 240, 241); border-bottom-color: rgb(36, 39, 41); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(36, 39, 41); border-left-style: none; border-left-width: 0px; border-right-color: rgb(36, 39, 41); border-right-style: none; border-right-width: 0px; border-top-color: rgb(36, 39, 41); border-top-style: none; border-top-width: 0px; box-sizing: border-box; font-family: Consolas,Menlo,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New,monospace,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 16.9px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline; white-space: pre;">Sheets("010 - RPL").Select LR = Cells(Rows.Count, "A").End(xlUp).Row Set Rng = Range("D6:D" & LR) Range("D1").Select Selection.Copy Rng.Select ActiveSheet.Paste </code>My current worksheet looks like this with the names on top as headers. Because the formula does not specify "empty cells only" to copy down formula, the code is overriding the second header. In the current example I have here, the vlookup formula is placed under "Dep Lookup header" where CTO Office and Engineering come the other sheet referenced. <code style="background-color: rgb(239, 240, 241); border-bottom-color: rgb(36, 39, 41); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(36, 39, 41); border-left-style: none; border-left-width: 0px; border-right-color: rgb(36, 39, 41); border-right-style: none; border-right-width: 0px; border-top-color: rgb(36, 39, 41); border-top-style: none; border-top-width: 0px; box-sizing: border-box; font-family: Consolas,Menlo,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New,monospace,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 16.9px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline; white-space: pre;">MainAccount Dept Dep Lookup January February March April Current Month YTD ------------------------------------------------------------------------ 40000 2020 CTO Office 75000 40000 0 60000 60000 175000 40000 2100 Engineering 0 0 0 40000 40000 40000 MainAccount Dept **#N/A** January February March April Current Month YTD ------------------------------------------------------------------------ 45000 2020 CTO Office 75000 40000 0 60000 60000 175000 46000 2100 Engineering 0 0 0 40000 40000 40000 </code>The page should look like this once the formula is created (notice that the third column in header does not change(Dep Lookup))... <code style="background-color: rgb(239, 240, 241); border-bottom-color: rgb(36, 39, 41); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(36, 39, 41); border-left-style: none; border-left-width: 0px; border-right-color: rgb(36, 39, 41); border-right-style: none; border-right-width: 0px; border-top-color: rgb(36, 39, 41); border-top-style: none; border-top-width: 0px; box-sizing: border-box; font-family: Consolas,Menlo,Monaco,Lucida Console,Liberation Mono,DejaVu Sans Mono,Bitstream Vera Sans Mono,Courier New,monospace,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 16.9px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline; white-space: pre;">MainAccount Dept Dep Lookup January February March April Current Month YTD ------------------------------------------------------------------------ 40000 2020 CTO Office 75000 40000 0 60000 60000 175000 40000 2100 Engineering 0 0 0 40000 40000 40000 MainAccount Dept **Dep Lookup** January February March April Current Month YTD ------------------------------------------------------------------------ 45000 2020 CTO Office 75000 40000 0 60000 60000 175000 46000 2100 Engineering 0 0 0 40000 40000 40000 </code>I would expect this to copy the formula from D1 and paste it into column D, starting from D6 and down to the last row of the sheet without overriding the various headers that I have in the page, meaning that it is only copying the formula into empty cells down to the last row.

<tbody style="border-bottom-color: rgb(36, 39, 41); border-bottom-style: none; border-bottom-width: 0px; border-image-outset: 0; border-image-repeat: stretch; border-image-slice: 100%; border-image-source: none; border-image-width: 1; border-left-color: rgb(36, 39, 41); border-left-style: none; border-left-width: 0px; border-right-color: rgb(36, 39, 41); border-right-style: none; border-right-width: 0px; border-top-color: rgb(36, 39, 41); border-top-style: none; border-top-width: 0px; box-sizing: border-box; font-family: Arial,"Helvetica Neue",Helvetica,sans-serif; font-size: 13px; font-size-adjust: none; font-stretch: 100%; font-style: normal; font-variant: normal; font-weight: 400; line-height: 16.46px; margin-bottom: 0px; margin-left: 0px; margin-right: 0px; margin-top: 0px; padding-bottom: 0px; padding-left: 0px; padding-right: 0px; padding-top: 0px; vertical-align: baseline;">
</tbody>

 

[Fluff]

How about

Sub deniseturan()
   With Sheets("010 - RPL")
      lr = .Cells(Rows.Count, "A").End(xlUp).Row
      .Range("D6:D" & lr).SpecialCells(xlBlanks).Formula = "=VLOOKUP(C1,'Departments Lookup'!$B:$D,3,FALSE)"
   End With
End Sub