Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
Debra Dalgleish shares some techniques:
http://www.contextures.com/xlDataEntry02.html Mascot wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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... -- HTH... Jim Thomlinson "Mascot" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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 -- Best wishes, Jim "Mascot" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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... -- HTH... Jim Thomlinson "Jim Thomlinson" wrote: 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... -- HTH... Jim Thomlinson "Mascot" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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" wrote: 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 -- Best wishes, Jim "Mascot" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help I need a macro or IF Statement
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 -- Best wishes, Jim "Mascot" wrote: 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" wrote: 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 -- Best wishes, Jim "Mascot" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro if and / or statement | Excel Discussion (Misc queries) | |||
If statement in macro | Excel Discussion (Misc queries) | |||
if then or statement in a macro | Excel Programming | |||
Can a Macro contain an IF statement? How? | Excel Programming | |||
Macro or IF statement??? HELP! | Excel Programming |