Add in specific cells

  • Thread starter Thread starter Seeker
  • Start date Start date
S

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
 
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
 
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?

Per Jessen said:
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 said:
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
Click to expand...
Click to expand...
 
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
 
Hi Jacob,
I just need to add one more End If, then it works perfectly, Thanks again
for both of you guys.
Best Regards
 
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
 
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

Seeker said:
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?

Per Jessen said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Hi Per Jessen,
Thank you very much, your codes works.
Regards

Per Jessen said:
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

Seeker said:
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?

Per Jessen said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top