Solved: Populate empty cells

[Slicemahn]

Hello Everyone!

I am trying to populate all rows of a sheet that is a result of a pasted pivot table. I have written some code in an effort to populate the cells but it overwrites or misses the key information. This task has turned out to be more challenging than I first imagined. Here is my code:

[VBA]Private Sub PopulateEmptyCells()
Dim EndRow As Integer
Dim RowLooper As Integer
With ActiveSheet
EndRow = Cells(65536, 1).End(xlUp).Row
For RowLooper = 7 To EndRow
If Cells(RowLooper, 2).Value = "" Or Cells(RowLooper, 2).Value = "" Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
Cells(RowLooper, 2) = Cells((RowLooper - 1), 2).Value
ElseIf Right(Cells(RowLooper, 2), 5) = "Total" And Cells(RowLooper, 3) = "" Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
Cells(RowLooper, 2).Font.FontStyle = "Bold"
ElseIf Cells(RowLooper, 1).Value = "" And Len(Cells(RowLooper, 2)) > 0 Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
ElseIf Right(Cells(RowLooper, 1), 5) = "Total" And Cells(RowLooper, 3) = "" Then
Cells(RowLooper, 1).Font.FontStyle = "Bold"
End If
Next RowLooper
End Sub[/VBA]

I appreciate all help and suggestions.

[Bob Phillips]

I guess you must be testing the wrong columns but soome explanation of the code's objectives would help so we can see. There are a couple of problems in the code, using Integer not Long, and checking the same value twice in the first test.

BTW, how come my name is in that list?

[Slicemahn]

Your name is in this list? It was totally random! Honest!

Yes you are right the first condition tests for the same cell which I agree is wrong. It should be :[VBA]

Private Sub PopulateEmptyCells()
Dim EndRow As Integer
Dim RowLooper As Integer
With ActiveSheet
EndRow = Cells(65536, 1).End(xlUp).Row
For RowLooper = 7 To EndRow
If Cells(RowLooper, 2).Value = "" Or Cells(RowLooper, 3).Value = "" Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
Cells(RowLooper, 2) = Cells((RowLooper - 1), 2).Value
ElseIf Right(Cells(RowLooper, 2), 5) = "Total" And Cells(RowLooper, 3) = "" Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
Cells(RowLooper, 2).Font.FontStyle = "Bold"
ElseIf Cells(RowLooper, 1).Value = "" And Len(Cells(RowLooper, 2)) > 0 Then
Cells(RowLooper, 1) = Cells((RowLooper - 1), 1).Value
ElseIf Right(Cells(RowLooper, 1), 5) = "Total" And Cells(RowLooper, 3) = "" Then
Cells(RowLooper, 1).Font.FontStyle = "Bold"
End If
Next RowLooper
End Sub

[/VBA]

I am trying to look at columns B and C to determine that if one or both are empty then populate/fill with the contents of the cells above. However it gets tricky: If you look at the attachment and the Desired Results tab, there are couple instances in which just column b needs to be populated. My code when executed doesn't capture the right categories. That is, it will overwrite info that should not be.
Incidentally what's your name, xld?

[Bob Phillips]

[vba]


Private Sub PopulateEmptyCells()
Dim EndRow As Long
Dim RowLooper As Long
With ActiveSheet
EndRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For RowLooper = 3 To EndRow
If .Cells(RowLooper, "A").Value = "" Then
If .Cells(RowLooper, "B").Value = "" And Cells(RowLooper, "C").Value = "" Then
.Cells(RowLooper, "B") = .Cells((RowLooper - 1), "B").Value
.Cells(RowLooper, "C") = .Cells((RowLooper - 1), "C").Value
ElseIf .Cells(RowLooper, "B").Value = "" And Cells(RowLooper, "C").Value <> "" Then
.Cells(RowLooper, "B") = .Cells((RowLooper - 1), "B").Value
ElseIf Right(.Cells(RowLooper, "C"), 5) = "Total" And .Cells(RowLooper, "B") = "" Then
.Cells(RowLooper, "B") = .Cells((RowLooper - 1), "B").Value
.Cells(RowLooper, "C").Font.FontStyle = "Bold"
ElseIf Right(.Cells(RowLooper, "B"), 5) = "Total" And .Cells(RowLooper, "C") = "" Then
.Cells(RowLooper, "B").Font.FontStyle = "Bold"
End If
End If
Next RowLooper
End With
End Sub
[/vba]