VBA Excel 2013 Write in ActiveCells in columns "C" and "D" then copy and paste until column "A" changes

Question

I am new to VBA and experiencing a first major problem. What I'm trying to do should be fairly easy. However, I can't seem to get it to work.

Right now, I am in the process of building a UserForm that simplifies entries in a dashboard of some sort.

I have already written code that writes values (of checked ListBox items and textboses) from the UserForm into cells, e.g. (Disclaimer: Might be the worst piece of code you've ever seen):

Public Sub Schreiben()

 Range("C" & (ActiveCell.row)).Value = frmEingabe.txtStatus.Value
 Dim listItems As String, i As Long

With frmEingabe.lstComment
    For i = 0 To .ListCount - 1
        If .Selected(i) Then listItems = listItems & .List(i) & ", "
    Next i
End With


If Len(listItems) > 0 And IsNull(frmEingabe.txtFrei) Then
    Range("D" & (ActiveCell.row)).Value = Left(listItems, Len(listItems) - 2)
Else
    If Len(listItems) = 0 And Not IsNull(frmEingabe.txtFrei) Then
          Range("D" & (ActiveCell.row)).Value = frmEingabe.txtFrei.Value
    Else
        If Len(listItems) > 0 And Not IsNull(frmEingabe.txtFrei) Then
        Range("D" & (ActiveCell.row)).Value = Left(listItems, Len(listItems) - 2) & ", " & frmEingabe.txtFrei.Value
        End If
    End If
End If
End Sub

The issue now is that it does this for only one row, namely the active row. However, I want an extra functionality that writes those values into cells in columns "C" and "D", copies these values and then pastes them in columns "C" and "D" until the value in column "A" changes.

Basically, I want to fill all rows of the same "type" (i.e. same value in "A", sorted by "A") with the exact same info but don't want to write individually, as this increases the calculation time of the dashboard immensely. For this reason, a For-Loop did not work for me, as it always took to long to paste into each cell individually (I didn't build the dashboard - every time you enter something in a cell it needs a few seconds to calculate what has happened).

I have already written a code to jump to the row where column "A" is different than before:

Private Sub btnJump_Click()
cmpgn = Range("A" & (ActiveCell.row)).Value
Do
Selection.Offset(1, 0).Select
Loop Until ActiveCell.EntireRow.Hidden = False And cmpgn <> Range("A" & (ActiveCell.row)).Value
End Sub

What would be the smartest way to combine both and have the code either:

Filldown columns "C" and "D" from the row where the code first writes values from the UserForm into Worksheet until the value in column "A" changes
Select ActiveCells.Row "C" & "D", copy their values, select cells in both columns until the value in column "A" changes and paste the values.

I hope my question is clear. If not, please let me know and I will try to clarify.

Thanks a bunch in advance!

You can always disable calculation (temporarily) using `Application.Calculcation = xlCalculationManual` and then re-enable it using `Application.Calculation = xlCaclulationAutomatic`. That would allow you to use the "FillDown" option without sacrificing performance. — David Zemens, Jul 16 '14 at 13:30
Also: You may want to read up on [how to avoid relying on `ActiveCell`, or the `Activate` and `Select` methods](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros). These are constructs you'll get from the Macro recorder, but which are generally to be avoided in VBA programming. — David Zemens, Jul 16 '14 at 13:31
Thanks! Inserting Application.Calculation.xCalculationManual and Application.Calculation.xCalculationAutomatic really helped with the issue. It runs for now - I can go into optimizing the code later on. — Sa Cid, Jul 16 '14 at 16:04
OK, I'll put that down as an answer; if you would "accept" it below, then this question may be visible and useful to others with similar problems in the future. Cheers. — David Zemens, Jul 16 '14 at 18:50

score 0 · Accepted Answer · answered Jul 16 '14 at 18:51

You can always disable calculation (temporarily) using Application.Calculcation = xlCalculationManual and then re-enable it using Application.Calculation = xlCaclulationAutomatic. That would allow you to use the "FillDown" option without sacrificing performance.

VBA Excel 2013 Write in ActiveCells in columns "C" and "D" then copy and paste until column "A" changes

1 Answers1