Add in specific cells

[Seeker]

Dear volunteers,
I got following script from the discussion group months ago to separate
groups of data with two empty rows base on cells in column A. Now I would
like to add text and formula in column D and E (in every first empty row) at
bottom of each groups, any ideahow can I do that?

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next

Regards

 

[Per Jessen]

Hi

Maybe something like this. I guess the approach for the formulas has to be
changed a bit once we know which formulas you need:

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Range("D" & lngRow) = "Total"
Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
Next

Regards,
Per

 

[Seeker]

Hi Jessen,
Thanks for your prompt reply. If my group contain one row only, then it is
fine, but if more than one row, data in cells of column D & E of the second
and onward rows will also changed to Total and the formula, how can I fix it
please?

Hi

Maybe something like this. I guess the approach for the formulas has to be
changed a bit once we know which formulas you need:

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Range("D" & lngRow) = "Total"
Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
Next

Regards,
Per

Dear volunteers,
I got following script from the discussion group months ago to separate
groups of data with two empty rows base on cells in column A. Now I would
like to add text and formula in column D and E (in every first empty row)
at
bottom of each groups, any ideahow can I do that?

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next

Regards

 

[Jacob Skaria]

Try the below

Sub Macro()
Dim lngRow As Long, lngLastRow As Long
lngLastRow = Cells(Rows.Count, 2).End(xlUp).Row + 1
For lngRow = lngLastRow To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then _
Rows(lngRow).EntireRow.Resize(2).Insert
If Range("A" & lngRow) = "" Then
Range("D" & lngRow) = "text"
Range("E" & lngRow).Formula = "=today()"
End If
Next
End Sub

If this post helps click Yes

 

[Seeker]

Hi Jacob,
I just need to add one more End If, then it works perfectly, Thanks again
for both of you guys.
Best Regards

 

[Seeker]

Hi Jacob,
What if I would like to add formula of =sum() in column N and O, how should
do I decide the dynamic range of each group on the start and end rows please?
Regards

 

[Per Jessen]

Hi

This should do it. You can just change the column reference to put formulas
in other columns.

Sub aaa()
Dim lngRow As Long
Range("A1").End(xlDown).Offset(1, 3) = "Total"
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
Range("D" & lngRow) = "Total"
End If
Next
lngRow = Range("D" & Rows.Count).End(xlUp).Row
FirstRow = 2
For r = 2 To lngRow
If Range("D" & r) = "Total" Then
Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")"
FirstRow = r + 2
End If
Next
End Sub

Regards,
Per

Hi Jessen,
Thanks for your prompt reply. If my group contain one row only, then it is
fine, but if more than one row, data in cells of column D & E of the
second
and onward rows will also changed to Total and the formula, how can I fix
it
please?

Hi

Maybe something like this. I guess the approach for the formulas has to
be
changed a bit once we know which formulas you need:

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Range("D" & lngRow) = "Total"
Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
Next

Regards,
Per

Dear volunteers,
I got following script from the discussion group months ago to separate
groups of data with two empty rows base on cells in column A. Now I
would
like to add text and formula in column D and E (in every first empty
row)
at
bottom of each groups, any ideahow can I do that?

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next

Regards

 

[Seeker]

Hi Per Jessen,
Thank you very much, your codes works.
Regards

Hi

This should do it. You can just change the column reference to put formulas
in other columns.

Sub aaa()
Dim lngRow As Long
Range("A1").End(xlDown).Offset(1, 3) = "Total"
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
Range("D" & lngRow) = "Total"
End If
Next
lngRow = Range("D" & Rows.Count).End(xlUp).Row
FirstRow = 2
For r = 2 To lngRow
If Range("D" & r) = "Total" Then
Range("E" & r).Formula = "=sum(E" & FirstRow & ":E" & r - 1 & ")"
FirstRow = r + 2
End If
Next
End Sub

Regards,
Per

Hi Jessen,
Thanks for your prompt reply. If my group contain one row only, then it is
fine, but if more than one row, data in cells of column D & E of the
second
and onward rows will also changed to Total and the formula, how can I fix
it
please?

Hi

Maybe something like this. I guess the approach for the formulas has to
be
changed a bit once we know which formulas you need:

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Range("D" & lngRow) = "Total"
Range("E" & lngRow).FormulaR1C1 = "=sum(r[-5]c:r[-1]c)"
Next

Regards,
Per

"Seeker" <[email protected]> skrev i meddelelsen
Dear volunteers,
I got following script from the discussion group months ago to separate
groups of data with two empty rows base on cells in column A. Now I
would
like to add text and formula in column D and E (in every first empty
row)
at
bottom of each groups, any ideahow can I do that?

Dim lngRow As Long
For lngRow = Cells(Rows.Count, 2).End(xlUp).Row To 2 Step -1
If Range("A" & lngRow) <> Range("A" & lngRow - 1) Then
Rows(lngRow).EntireRow.Resize(2).Insert
End If
Next

Regards

 

[Per Jessen]

Thanks for your reply, I am glad to help.

/Per