Insert a value into a cell after a row has been inserted.

[gj0519]

I am using vba to export data into an Excel spreadsheet that sorts that data in column "B". I then insert 2 rows after each group. Out of those 2 rows I want to insert a Specific value into the the empty row inserted under each group in Column "B". Then insert 2 formulas in the same row but 1 in column "C" and the other in column "D".
Here is my code that inserts 2 rows with the help from firefytr.

With Sheets("All Loans")
LastRow = 3
For x = .Range("B65536").End(xlUp).Row To LastRow Step -1
If .Range("B" & x) <> .Range("B" & x - 1) Then Cells(x, 2).Resize(2, 1).EntireRow.Insert
Next x
End With

Here is the code I am working with to insert the first value.

Private Sub CommandButton1_Click()
Dim LastRow As Long, x As Long
With Sheets("All Loans")
LastRow = 3
For x = .Range("B65536").End(xlUp).Row To LastRow Step -1
If .Range("B" & x) <> .Range("B" & x - 1) Then _
Cells(x, "B").Value = "Totals:" & Cells(x, "B").Value
Next x
End With
End Sub

This code works but gives me an extra "Totals" for each group.

Thanks for the help,

Glenn

[gj0519]

With some help I was able to get what I was after.
I had to adjust 2 lines of my 2nd code from above. Here is what it is if anyone is interested.

Private Sub CommandButton1_Click()
Dim LastRow As Long, x As Long
With Sheets("All Loans")
LastRow = 3
For x = .Range("B65536").End(xlUp).Row + 1 To LastRow Step -1
If .Range("B" & x) = "" And .Range("B" & x + 1) = "" Then _
Cells(x, "B").Value = "Totals:" & Cells(x, "B").Value
Next x
End With
End Sub

Thanks

-Glenn

[Zack Barresse]

Hey Glenn,

Great to see you got it working on your own!!!

One question, is this line ...

Cells(x, "B").Value = "Totals:" & Cells(x, "B").Value

.. supposed to be referenced in the With statement? .. periods prior to the Cells?

[gj0519]

Thanks,

I did not notice that. It still worked with that in there but it should be like below.
1 thing I am trying to do, but not quite there is align just TOTALS: to the right. I have tried a few different things but it aligns my entire column to the right. I use .HorizontalAlignment = xlright.
Any tips?

-Glenn

With Sheets("All Loans") ' Insert's the word TOTALS: after each group
LastRow = 3 
For x = .Range("B65536").End(xlUp).Row + 1 To LastRow Step -1
If .Range("B" & x) = "" And .Range("B" & x + 2) = "" Then _
Cells(x, "B").Value = "TOTALS:"

[Zack Barresse]

Well, about the periods first. They will work if the activesheet is the sheet which you are working on. But, the first time you try and run this on another sheet, your results will be disasterous. Always explicitly show where a range will be from (sheet/book) if possible. Try it out on some sample data/workbook.

Maybe you could try this ...

.Cells(x, "B").Value = "Totals:" & .Cells(x, "B").Value
.Cells(x, "B").HorizontalAlignment = xlRight

Do you mean that you are trying to put "TOTALS:" in a cell other than that which you are putting the value? Maybe I'm not following..

[gj0519]

Thanks for explaining how the periods work.
For TOTALS: I don't need it inserted into any other cell(s).
And that alignment worked for me also.

Thanks,

-Glenn