PDA

View Full Version : Solved: Populate empty cells



Slicemahn
12-20-2007, 01:20 AM
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:

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

I appreciate all help and suggestions.

Bob Phillips
12-20-2007, 02:49 AM
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
12-20-2007, 03:08 AM
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 :

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


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
12-20-2007, 06:16 AM
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