help me with this code

meline12

New Member
Joined
Jan 22, 2004
Messages
10
I have a text column that lists owners. I have another column that gives the account number associated with the owner. However, not all the account numbers are filled in. So i want to automate the process of putting in the correct account numbers. Many of the owners are listed more than once in a row, but only one row has an account number. Thus I want a macro to fill in those other account numbers which should be the same. For example, my situation is the following:

Owner Account
Art Stovall____________432___
Art Stovall__________________
Art Stovall__________________
Steven Superior____________
Steven Superior_______443__
Michelle Norris________967___
Michelle Norris________ 987___
Michelle Norris______________

As you can see, Art Stovall is listed 3 times but only one account number is listed. I want the macro to fill in the rest of the blanks with the account number 432. Also, Michelle Norris has 2 different account numbers, and i want the blank account to be the last number listed (987). Likewise, with Steven Superior, the blank account should be 443.

Any help would be great!
 

Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
You don't need a macro to do this, but since you are asking for one, this should do what you want, assuming your list represents the names in column A and the numbers in column B.

Sub Test1()
With Range("B2:B" & Cells(Rows.Count, 1).End(xlUp).Row)
.SpecialCells(4).Formula = "=R[-1]C"
.Value = .Value
End With
End Sub


For a non-VBA solution:

Step 1
Select range (assuming your data startis in row 1) B2, down to the last cell in column B where a name exists is column A.

Step 2
Then click on Edit > Special > GoTo > Blanks, OK.

Step 3
Press the equal sign, and click in cell B1

Step 4
Press Ctrl and the Enter key.

Step 5
Optional, select all of column B, click Edit > Copy, then press Edit > Paste Special for Values > OK > Esc
 

meline12

New Member
Joined
Jan 22, 2004
Messages
10
It's not working the way i need it to. Does anyone else have any suggestions??
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
Yes here's a suggestion. Tell us what you really want and why the code or the manual steps are not doing what you want, instead of just saying "It's not working the way i need it to.".

The macro works fine, and the steps work fine too if you follow them. They were all successfully tested before being posted based on your original description. Maybe you need to edit the macro to suit your actual range, which you still haven't told us.
 

lbass

Board Regular
Joined
Aug 15, 2002
Messages
104

ADVERTISEMENT

I'm having a hard time converting to VBA.
This caught my eye and I was trying to figure out what your code did,
So I stepped through it and ... don't know how or what it did, but would like too. Could you explain what each statement does for a real BLOCK head Please?

By the way it didn't do what he wanted but I am very interested in what it did.

Owner Account
Art Stovall 432 432
Art Stovall 432 432
Art Stovall 432 432
Steven Superior 432 443
Steven Superior 443 443
Michelle Norris 967 987
Michelle Norris 987 987
Michelle Norris 987 987

The left column is what I got with your macro The right column is what I
believe he wanted.

Never mind that please does Range("B2:B" give you all from B2 -> B60,000
or where ever?

What does .SpecialCells(4).Formula mean?
What does .Value = .Value do?

I was thinking this would work for something I'm trying to do but am not
smart enough to know or not?

Thanks if you are willing to help.
 

meline12

New Member
Joined
Jan 22, 2004
Messages
10
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...
 

Tom Urtis

MrExcel MVP
Joined
Feb 10, 2002
Messages
11,208
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
 

lbass

Board Regular
Joined
Aug 15, 2002
Messages
104
Yes I read his request wrong. When he said 2 account numbers and for the last to be the one for the blank. I misread this to say the last number should be for all. Sorry.

I really want to thank you for this very elegant code. I had been looking for something like this for awhile but this was so slick that I could not tell if it would do what I needed or not. It will.

Thanks too for all that took the time to explain what was going on. I'm trying real hard to get Lotus out of my head. There's just enough difference to make it very difficult for me to realize the finer points of code.

Thanks for taking the time to help the helpless.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,057
Messages
5,545,753
Members
410,704
Latest member
Cobber2008
Top