meline12 said:
Thanks Tom for the great feedback. It works pretty good but like Ibass said, I need the account numbers to stay with the owner. Thus, the end result should look like this:
Art 432
Art 432
Art 432
Steven 443 (the macro here gives an acct no of 432)
Steven 443
Michelle 967
Michelle 987
Michelle 987
thanks for your help...
That's not exactly what Ibass said. Take a close look at your desired result for the first instance of Michelle. You want it to remain at 967 but Ibass said you want it at 987. I think you want it at 967 because it was not blank to begin with.
I also think there are are a lot of potential scenarios with this. For example, when Steven's name shows up for the first time his account number is blank, but we cannot assume his name on the next row down will have an account number. There might be 10 or 50 rows of Steven until his account number shows up for the first time, in case this is an import text file which it sort of looks like.
Anyway, here is a solution that works based on my understanding of the problem. I see Ibass asked about my original code in a separate thread which other people kindly answered (thanks guys !). In case Ibass is looking in on this, I commented each line so he and / or you can follow along.
I've given up on you telling us what the columns actually are, so I am assuming again that the names are in column A and the account numbers are in column B. I'm also assuming the names are not scattered everywhere down A, but sorted as you depicted it.
The following macro is just one approach; there are other ways to do it and there's always more than one solution to a problem. We could have avoided a loop by using a formula in an extra column as the evauation tool, but there would have been so many nested Ifs and non-blank cells being unnecessarily evaluated that it would not have been worth it in my opinion. A filter would not have given us much of an edge either, because we'd need to loop anyway for the first visible row with an account number in column B.
Tested fine on XL2K3 XP.
Sub Test2()
'Turn off screen updating to speed the macro
Application.ScreenUpdating = False
'Declare a variable for each cell to be evaluated
Dim x As Range
'Define the loop to involve blank cells in column B, down to and including the last used row in column A
For Each x In Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(4)
'Use a With structure for the x variable so you don't type it in all the time, which also speeds the macro
With x
'Case structure to deal with a number of different possible scenarios for each empty cell
Select Case True
'If the saleperson's name in the same row of column A equals the name in the next row up in column A...
Case .Offset(0, -1) = .Offset(-1, -1)
'...the blank cell's value shall equal the value of the cell above it.
.Value = .Offset(-1, 0)
'If the saleperson's name in the same row of column A does not equal the name in the next down in column A...
Case Else
'...two sub-scenarios - -
' the first sub-scenario is if the cell below is not empty.
' the second sub-scenario is if the cell below is empty
'If the cell below is not empty (first sub-scenario)...
If Len(.Offset(1, 0)) <> 0 Then
'...the blank cell being evaluated shall equal the value of that non-empty cell below
.Value = .Offset(1, 0)
'Second sub-scenario, if the cell below is empty...
Else
'...the blank cell's value shall be that of the first instance of the next account number in column B for that salesperson
.Value = Cells(.Row, 2).End(xlDown)
'Terminate the If structure
End If
'Terminate the Case structure
End Select
'Close the With structure
End With
'Move on to the next blank cell
Next x
'Turn screen updating back on
Application.ScreenUpdating = True
End Sub