PDA

View Full Version : [SOLVED] Insert a value into a cell after a row has been inserted.



gj0519
04-26-2005, 11:02 AM
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 (http://www.vbaexpress.com/forum/showthread.php?t=2894).


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
04-28-2005, 09:57 AM
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
04-28-2005, 10:11 AM
Hey Glenn,

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

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
04-28-2005, 03:46 PM
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
:thumb


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
04-29-2005, 08:52 AM
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
04-29-2005, 03:36 PM
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