Help I need a macro or IF Statement

[Mascot]

Hi Everyone,

I have a spreadsheet that looks like the one below. You will notice that I
will have an Acct # listed and then it will have Debits & Credits and then it
will list the account and then sub account. The problem is that wherever
there is an Acct. # with more than one account or sub account the Acct. # is
only listed with the first Acct. I need to have this copied down on each
line were there is an Acct. for each Acct. #. Can someone please help me
with this?

ACCT # DEBIT CREDIT ACCT SubAcct.
Z000100RER 100 -200 7001 9999
8001 9999
8100 9999
8500 9999
8600 9999
8700 9999
8800 9999

Z000300RER 300 0 7001 8888
8390 8888

Z000400RER 400 0 7001 7777

Z000500RER 500 -600 7001 5555

Z000600RER 800 -700 7001 4444

Z000700RER 1000 -900 7001 3333

Z100100RER 1200 -1100 1001 0

Z100200RER 1400 -1300 1002 20000

Z100300RER 1600 -1500 1003 0

Z100400RER 1700 1700 1004 0

Z100500RER 1900 -1800 1005 79000

Z505000RER 2000 2000 5050 0
5050 200
5050 201
5050 201
5050 203
5050 001B3
5050 400
5050 HR500
5050 HR501
5050 HR502

Z606000RER 2100 2100 6060 15000

Z606001RER 2200 0 6060 15010

Z606500RER 2300 2300 6065 15020

Z605001RER 2400 -2500 6065 15030

Z606600RER 2600 -2700 6066 15040

Z606601RER 2800 0 6066 15050

 

[Dave Peterson]

Debra Dalgleish shares some techniques:
http://www.contextures.com/xlDataEntry02.html

 

[Jim Thomlinson]

There is a trick to doing this... In cell A3 add the formula =A2. Now you
have filled in one of the blanks that you need filled. Copy this cell (A3).
Select the entire area where you need to fill in the blanks (A3:A???). Now
Hit F5 -> Special Cells -> Blanks -> OK. Now all of the blank cells should be
selected. Press Ctrl + V to paste the formula that you copied. Now all of the
blnks should be filled in. The final step will be to copy and paste special
values the cells that you just filled in with formulas...

 

[Jim Jackson]

Here is a macro that will do the job.

Sub fill_in()
ActiveSheet.Range("A3").Select
For Each cell In Sheets
Do
If ActiveCell > "" Then

ElseIf ActiveCell.Offset(0, 3) > "" Then
ActiveCell = ActiveCell.Offset(-1, 0)
ActiveCell.Offset(1, 0).Activate
ElseIf ActiveCell = "" And ActiveCell.Offset(1, 0) = "" Then
Exit For
End If
ActiveCell.Offset(1, 0).Activate
Loop
Next
End Sub

 

[Jim Thomlinson]

Sorry I just noticed that you have blanks in your Acct (I thought it was a
text wrap thing). Instead of the formula =A2 use the formula =if(D3<>"", A2,
"")... Otherwise the process should work just fine...

 

[Mascot]

HI Jim,

Thanks for this macro. I tried it on my sheet and it works. However it
stopped after it filled in the first blank under the Acct # and then moved to
the next Acct #. Is ther some code that I need to add?

Thanks
Mascot

 

[Jim Jackson]

I sent this once but have not seen it appear. I failed to delete a line
before sending originally. Sorry about that.

Sub fill_in()
ActiveSheet.Range("A3").Select
For Each cell In Sheets
Do
If ActiveCell > "" Then

ElseIf ActiveCell.Offset(0, 3) > "" Then
ActiveCell = ActiveCell.Offset(-1, 0)

ElseIf ActiveCell = "" And ActiveCell.Offset(1, 0) = "" Then
Exit For
End If
ActiveCell.Offset(1, 0).Activate
Loop
Next
End Sub