Add a value into a certain cell and column if row is empty.

[gj0519]

I have around 200 rows of data with 2 empty rows separating each group. I would like to insert a specific value into the upper most row for each group in column 'B'.
Here is my code I am trying to get to work. As of now it is giving me 2 values for each group. Hope this makes since.

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

 

[TonyJollans]

Hi gj0519,

You are checking if cells in column B are not equal to the one above. What you say you want is empty cells with an empty cell below (in column B). To change your code you should do a check something like this instead:

If IsEmpty(.Range("B" & x)) And IsEmpty(.Range("B" & x + 1)) Then _

or, if that fails, try:

If .Range("B" & x) = "" And .Range("B" & x + 1) = "" Then _

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[gj0519]

Thanks Tony,

Both of those work, the 1 thing it does not do is insert my "Totals:" value after my last record. If there is another value following my last record it will insert otherwise it finishes. Any suggestions on what I could do.

Thanks,


-Glenn

 

[TonyJollans]

Hi Glenn,

It inserts nothing at the bottom because

[purple] For x = [b].Range("B65536").End(xlUp).Row[/b] To LastRow Step -1[/purple]

starts the loop at the last non-empty row. Starting one row lower should do the trick:

[blue] For x = .Range("B65536").End(xlUp).Row [highlight]+ 1[/highlight] To LastRow Step -1[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[gj0519]

That was it.
I appreciate the help. The more I keep doing the more I am learning.

Thanks,

-Glenn