How to put formulas into blank cells from VBA when something isentered?

[S Davis]

Hey, sorry for the title. It's late here in Africa. Happy new year
btw!

I need two relatively simple things.

1) I have a recorded macro that just copies a bunch of cells and then
pastes them into the row below. Trouble is, they have direct cell
references (ie. "B3:F3") which I would rather just be referred to as
offsets from the activecell. Unsure how to do this in VBA though.

2) I would only like this macro to initiate once text is entered into
a cell in column A. For instance, if the user enters text into cell
A2, the macro should then initiate, copy all formulas from B2:F2, and
paste them into B3:F3. If the user then enters text into A3, it should
follow that it copies B3:F3 and pastes them into B4:F4.

In summary:
Once text is entered in to cell A2, the macro initiates and copies
cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
macro. Of course, not ever referencing the cell by its name directly.

The purpose for all this is to save file size of course, rather than
having 65000*5 cells sitting there with inactive formulas jsut waiting
for text to go into the A column. I hope this makes sense. Thanks!

 

[Lars-Åke Aspelin]

Hey, sorry for the title. It's late here in Africa. Happy new year
btw!

I need two relatively simple things.

1) I have a recorded macro that just copies a bunch of cells and then
pastes them into the row below. Trouble is, they have direct cell
references (ie. "B3:F3") which I would rather just be referred to as
offsets from the activecell. Unsure how to do this in VBA though.

2) I would only like this macro to initiate once text is entered into
a cell in column A. For instance, if the user enters text into cell
A2, the macro should then initiate, copy all formulas from B2:F2, and
paste them into B3:F3. If the user then enters text into A3, it should
follow that it copies B3:F3 and pastes them into B4:F4.

In summary:
Once text is entered in to cell A2, the macro initiates and copies
cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
macro. Of course, not ever referencing the cell by its name directly.

The purpose for all this is to save file size of course, rather than
having 65000*5 cells sitting there with inactive formulas jsut waiting
for text to go into the A column. I hope this makes sense. Thanks!

Try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 1 Then
Target.Offset(0, 1).Resize(1, 5).Copy
Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
Application.CutCopyMode = False
End If
End Sub

Hope this helps / Lars-Åke

 

[S Davis]

Try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Column = 1 Then
     Target.Offset(0, 1).Resize(1, 5).Copy
     Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
     Application.CutCopyMode = False
   End If
End Sub

Hope this helps / Lars-Åke

Wow! I can't even tell what is happening there. Can you explain how it
realizes the change in the cell and then copies the formulas down?

Many thanks,
_Sean

 

[JLGWhiz]

I didn't test this but it should work. Put it in the worksheet code module
for the sheet with the formulas. It assumes one header row.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lr As Long
lr = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
If Target = Range("A" & lr + 1) Then
Range("B" & Target.Row & ":F" & Target.Row).Copy
Range("B" & Target.Row + 1).PasteSpecial _
Paste:=xlPasteFormulas
End If
End Sub

 

[Lars-Åke Aspelin]

Wow! I can't even tell what is happening there. Can you explain how it
realizes the change in the cell and then copies the formulas down?

Many thanks,
_Sean

The Worksheet_Change sub is a predefined macro that is triggered
whenever there are any changes in any cells of the worksheet.

The (also predefined) parameter Target holds a reference to the cell
that has been changed.
In this case we are only interested in changes to column A (which has
the value 1) therefore the If statement.

The remaining three statements is the copying of Bn:Fn to Bn+1:Fn+1.
Example: If changes are made to cell A2 then
Target = A2
Target.Column = 1 (so this should be handled)
Target.Offset(0,1) = B2
Target.Offset(0,1).Resize(1,5) = B2:F2
Target.Offset(1,1) = B3

Application.CutCopyMode = False
is just to end the copying mode.

Lars-Åke

 

[JLGWhiz]

Lars' code does the same as mine, only his executes for any selection in
column one and mine only executes if the selection is the next empty cell in
column one.

 

[Gord Dibben]

Try this in your sheet module.

Assumes you have formulas in B1:F1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Target.Cells.Column = 1 Then
n = Target.Row
If Me.Range("A" & n).Value <> "" Then
With Target
..Offset(-1, 1).Resize(, 5).Copy _
Destination:=.Offset(, 1)
End With
End If
End If
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP

 

[S Davis]

Lars' code does the same as mine, only his executes for any selection in
column one and mine only executes if the selection is the next empty cellin
column one.

Well it works excellent. However, dumb as I may be, I can not get it
to select the next cell in line after the macro has completed. Simply,
if I enter text inbto A5, I need it to finish by selecting A6, or
whatever it is. activecell.offset(0,-2).select is not working for some
reason, though positive values do work....

 

[S Davis]

Try this in your sheet module.

Assumes you have formulas in B1:F1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
    Application.EnableEvents = False
If Target.Cells.Column = 1 Then
        n = Target.Row
If Me.Range("A" & n).Value <> "" Then
With Target
.Offset(-1, 1).Resize(, 5).Copy _
             Destination:=.Offset(, 1)
End With
        End If
    End If
enditall:
    Application.EnableEvents = True
End Sub

Gord Dibben  MS Excel MVP

Much nicer, thank you Mr. Savant. I love this group Thank you Gord
and JLWhiz as well, just saved me 7 meg of space and taught me a ton!
-Sean in Africa