Fill down formula if other column contains data

[RichardRosema]

Hi

I'm trying to get a macro to fill down formulas in 2 columns when a
new row is inserted, depending on whether there is data in column B.
i.e. Only fill down in columns W and X if B is not empty for that row.

I've tried hashing a sub together from another thread but without
luck. (Rows 1 to 7 contain heading information, etc.)
Any help would be appreciated.
My hashed sub (as a shockingly novice user) is this:

Sub fill_formula_until_end()

' Copy Formula W7 & then paste down until empty Rows
Range("W7").Select
Row = 2
Do Until IsEmpty(Cells(Row, 2))
Cells(Row, 23).Select
Selection.FillDown
Row = Row + 1
Loop
Row = Row - 1

' Copy Formula X7 & then paste down until empty Rows
Range("X7").Select
Selection.Copy
Row = 2
Do Until IsEmpty(Cells(Row, 2))
Cells(Row, 24).Select
ActiveSheet.Paste
Row = Row + 1
Loop
Row = Row - 1

End Sub

I'll be honest and admit that I'm not sure where in the above code it
checks if column B is empty or not, to know whether a fill down would
apply.

P.S. I can't use the copy formula because I'm using the following
private sub in the workbook:
Application.CutCopyMode = False
Application.CellDragAndDrop = False

 

[Niek Otten]

Did you look at Tools>Options>Edit tab, Extend data range formats and formulas?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

| Hi
|
| I'm trying to get a macro to fill down formulas in 2 columns when a
| new row is inserted, depending on whether there is data in column B.
| i.e. Only fill down in columns W and X if B is not empty for that row.
|
| I've tried hashing a sub together from another thread but without
| luck. (Rows 1 to 7 contain heading information, etc.)
| Any help would be appreciated.
| My hashed sub (as a shockingly novice user) is this:
|
| Sub fill_formula_until_end()
|
| ' Copy Formula W7 & then paste down until empty Rows
| Range("W7").Select
| Row = 2
| Do Until IsEmpty(Cells(Row, 2))
| Cells(Row, 23).Select
| Selection.FillDown
| Row = Row + 1
| Loop
| Row = Row - 1
|
| ' Copy Formula X7 & then paste down until empty Rows
| Range("X7").Select
| Selection.Copy
| Row = 2
| Do Until IsEmpty(Cells(Row, 2))
| Cells(Row, 24).Select
| ActiveSheet.Paste
| Row = Row + 1
| Loop
| Row = Row - 1
|
| End Sub
|
| I'll be honest and admit that I'm not sure where in the above code it
| checks if column B is empty or not, to know whether a fill down would
| apply.
|
| P.S. I can't use the copy formula because I'm using the following
| private sub in the workbook:
| Application.CutCopyMode = False
| Application.CellDragAndDrop = False

 

[Joel]

Sub fill_formula_until_end()

'find last row in column B
LastRow = Range("B" & rows.Count).end(xlup).Row
for Rowcount = 9 to LastRow
if Range("B" & RowCount) <> "" then
Range("W" & RowCount) = Range("W8")
Range("X" & RowCount) = Range("X8")
end if
next RowCount

 

[Richard R]

Checking or unchecking this option doesn't change the result of the
macro. I'm needing to use a macro because the workbook will be
completed by other users and columns W & X are protected, therefore
users will not/should not be able to fill the formula themselves.

Thanks for your response but any idea where I'm going wrong with the
code?

 

[Richard R]

Sub fill_formula_until_end()

'find last row in column B
LastRow = Range("B" & rows.Count).end(xlup).Row
for Rowcount = 9 to LastRow
   if Range("B" & RowCount) <> "" then
         Range("W" & RowCount) = Range("W8")
         Range("X" & RowCount) = Range("X8")
   end if
next RowCount

Joel

This leaves the formulas in cells W8 and X8 but the cells below are
hardcoded with whatever value was in W8 and X8, rather than entering
the formula

Any idea what change I need to make to your code?
Thanks