Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE help ...
Hello -
I've put my code below ... here's the issue I'm running into: The first Sub (UpdateJournal) contains 2 Loops -- one that pulls in data from a sheet called 'Advances' and the other pulls from 'Out-of- State Sales' and puts this data on the 'Journal' sheet. Both loops contain a line that reads: amount=ActiveCell.value This 'amount' is then moved to the Journal sheet ... except that it doesn't work in the 'Out-of-State Sales' loop and I have no idea why. I used the Watch window to track the values of each variable and everything reads the way that it should. I even tried this code in the Immediate window: (from a cell on Journal sheet) ActiveCell.value = Sheets("Out-of-State Sales").Range("D18").Value This worked fine ... so I'm assuming there's no format issues ... Why would this be happening and how to fix it? Thanks VERY much in advance, ray Sub UpdateJournal() Dim Add As String Application.ScreenUpdating = False Application.EnableEvents = False ' 1st loop starts here Sheets("advances").Activate Range("g10").Select If IsEmpty(ActiveCell.Value) = True Then ActiveCell.Offset(1, 0).Activate End If Do While IsEmpty(ActiveCell.Value) = False amount = ActiveCell.Value PJ = ActiveCell.Offset(0, -1).Value CC = ActiveCell.Offset(0, -2).Value BusUnit = ActiveCell.Offset(0, -3).Value CompFunc = ActiveCell.Offset(0, -4).Value AcctNum = ActiveCell.Offset(0, -5).Value Description = ActiveCell.Offset(0, -6).Value Sheets("Journal").Activate Range("M75").Activate Do While IsEmpty(ActiveCell.Value) = False ActiveCell.Offset(1, 0).Activate Loop ActiveCell.Value = 0 ActiveCell.Offset(0, -1).Value = amount ActiveCell.Offset(0, -5).Value = PJ ActiveCell.Offset(0, -6).Value = CC ActiveCell.Offset(0, -7).Value = BusUnit ActiveCell.Offset(0, -8).Value = CompFunc ActiveCell.Offset(0, -10).Value = AcctNum ActiveCell.Offset(0, -11).Value = Description ActiveCell.Offset(0, 3).Value = Description Sheets("Advances").Activate ActiveCell.Offset(1, 0).Activate Loop ' 2nd Loop starts here Sheets("out-of-state sales").Activate LineNum = 9 Range("D9").Activate For LineNum = 9 To 47 If IsEmpty(ActiveCell.Value) = False Then amount = ActiveCell.Value Tax = ActiveCell.Offset(0, 1).Value County = ActiveCell.Offset(0, -2).Value Sheets("oos").Cells(4, 2).Value = County CodeNum = Sheets("oos").Cells(4, 3).Value County_Tax = Sheets("oos").Cells(3, 2).Value StorePrefix = Sheets("oos").Cells(4, 5).Value Sheets("Journal").Activate Range("m75").Activate Do While IsEmpty(ActiveCell.Value) = False ActiveCell.Offset(1, 0).Activate Loop InsertValues End If ActiveCell.Offset(1, 0).Activate Next LineNum Application.ScreenUpdating = True Application.EnableEvents = True End Sub Sub InsertValues() ActiveCell.Value = amount ActiveCell.Offset(0, -1).Value = "0" ActiveCell.Offset(0, -5).Value = StorePrefix 'PJ/SA code ActiveCell.Offset(0, -6).Value = "CC3000" ActiveCell.Offset(0, -7).Value = Worksheets("Cover").Range("H7") 'Store # ActiveCell.Offset(0, -8).Value = Worksheets("Cover").Range("H8") 'Retail Unit ActiveCell.Offset(0, -10).Value = "3011" ActiveCell.Offset(0, -11).Value = County_Tax ActiveCell.Offset(0, 3).Value = County_Tax 'Do The Tax ActiveCell.Offset(1, 0).Activate ActiveCell.Value = Tax ActiveCell.Offset(0, -1).Value = "0" ActiveCell.Offset(0, -7).Value = Worksheets("Cover").Range("H7") ActiveCell.Offset(0, -8).Value = CodeNum ActiveCell.Offset(0, -10).Value = "2671" ActiveCell.Offset(0, -11).Value = County_Tax ActiveCell.Offset(0, 1).Value = County_Tax Sheets("Out-of-State Sales").Activate End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE help ...
hard without seeing data, but if i follow you, you want to InsertValues on
the "Journal" sheet which is what you are doing. Then you increment the row on "Journal" and then loop, i'm not sure whether you wanted to increment on "Journal" or "Out-of-state sales". then you loop InsertValues End If ActiveCell.Offset(1, 0).Activate Next LineNum which is fine, but you are still on "Journal" at this point For LineNum = 9 To 47 If IsEmpty(ActiveCell.Value) = False Then amount = ActiveCell.Value and you continue working on "Journal" instead of reselecting "Out-of-state sales". if my guess is right you want to change End If Sheets("Out-of-state sales").Activate ActiveCell.Offset(1, 0).Activate Next LineNum to -- -John Please rate when your question is answered to help us and others know what is helpful. "Ray" wrote: Hello - I've put my code below ... here's the issue I'm running into: The first Sub (UpdateJournal) contains 2 Loops -- one that pulls in data from a sheet called 'Advances' and the other pulls from 'Out-of- State Sales' and puts this data on the 'Journal' sheet. Both loops contain a line that reads: amount=ActiveCell.value This 'amount' is then moved to the Journal sheet ... except that it doesn't work in the 'Out-of-State Sales' loop and I have no idea why. I used the Watch window to track the values of each variable and everything reads the way that it should. I even tried this code in the Immediate window: (from a cell on Journal sheet) ActiveCell.value = Sheets("Out-of-State Sales").Range("D18").Value This worked fine ... so I'm assuming there's no format issues ... Why would this be happening and how to fix it? Thanks VERY much in advance, ray Sub UpdateJournal() Dim Add As String Application.ScreenUpdating = False Application.EnableEvents = False ' 1st loop starts here Sheets("advances").Activate Range("g10").Select If IsEmpty(ActiveCell.Value) = True Then ActiveCell.Offset(1, 0).Activate End If Do While IsEmpty(ActiveCell.Value) = False amount = ActiveCell.Value PJ = ActiveCell.Offset(0, -1).Value CC = ActiveCell.Offset(0, -2).Value BusUnit = ActiveCell.Offset(0, -3).Value CompFunc = ActiveCell.Offset(0, -4).Value AcctNum = ActiveCell.Offset(0, -5).Value Description = ActiveCell.Offset(0, -6).Value Sheets("Journal").Activate Range("M75").Activate Do While IsEmpty(ActiveCell.Value) = False ActiveCell.Offset(1, 0).Activate Loop ActiveCell.Value = 0 ActiveCell.Offset(0, -1).Value = amount ActiveCell.Offset(0, -5).Value = PJ ActiveCell.Offset(0, -6).Value = CC ActiveCell.Offset(0, -7).Value = BusUnit ActiveCell.Offset(0, -8).Value = CompFunc ActiveCell.Offset(0, -10).Value = AcctNum ActiveCell.Offset(0, -11).Value = Description ActiveCell.Offset(0, 3).Value = Description Sheets("Advances").Activate ActiveCell.Offset(1, 0).Activate Loop ' 2nd Loop starts here Sheets("out-of-state sales").Activate LineNum = 9 Range("D9").Activate For LineNum = 9 To 47 If IsEmpty(ActiveCell.Value) = False Then amount = ActiveCell.Value Tax = ActiveCell.Offset(0, 1).Value County = ActiveCell.Offset(0, -2).Value Sheets("oos").Cells(4, 2).Value = County CodeNum = Sheets("oos").Cells(4, 3).Value County_Tax = Sheets("oos").Cells(3, 2).Value StorePrefix = Sheets("oos").Cells(4, 5).Value Sheets("Journal").Activate Range("m75").Activate Do While IsEmpty(ActiveCell.Value) = False ActiveCell.Offset(1, 0).Activate Loop InsertValues End If ActiveCell.Offset(1, 0).Activate Next LineNum Application.ScreenUpdating = True Application.EnableEvents = True End Sub Sub InsertValues() ActiveCell.Value = amount ActiveCell.Offset(0, -1).Value = "0" ActiveCell.Offset(0, -5).Value = StorePrefix 'PJ/SA code ActiveCell.Offset(0, -6).Value = "CC3000" ActiveCell.Offset(0, -7).Value = Worksheets("Cover").Range("H7") 'Store # ActiveCell.Offset(0, -8).Value = Worksheets("Cover").Range("H8") 'Retail Unit ActiveCell.Offset(0, -10).Value = "3011" ActiveCell.Offset(0, -11).Value = County_Tax ActiveCell.Offset(0, 3).Value = County_Tax 'Do The Tax ActiveCell.Offset(1, 0).Activate ActiveCell.Value = Tax ActiveCell.Offset(0, -1).Value = "0" ActiveCell.Offset(0, -7).Value = Worksheets("Cover").Range("H7") ActiveCell.Offset(0, -8).Value = CodeNum ActiveCell.Offset(0, -10).Value = "2671" ActiveCell.Offset(0, -11).Value = County_Tax ActiveCell.Offset(0, 1).Value = County_Tax Sheets("Out-of-State Sales").Activate End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE help ...
Hi again -
Actually, the macro 'works' 99.9% exactly as it should ... at least according to what I saw via the Watch Window. The problem comes on on the 2nd loop, when the InsertValues macros is triggered -- the line 'ActiveCell.value = amount' line results in a blank cell, even though 'amount' is a positive value (as confirmed in Watch Window). So, why would the code insert a blank cell where an available value exists? In fact, the InsertValues macro wouldn't have been called if 'amount' wasn't a non-blank value in the first place! The cell formats look OK and no error is produced ... Very odd! TIA, Ray |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
PLEASE help ...
Hi
IsEmpty checks if a variable has been initialised or not and should be used with caution. Use If ActiveCell.Value<0 or If ActiveCell.Value<"" to test the cell value instead. regards Paul On Feb 25, 11:50*am, Ray wrote: Hi again - Actually, the macro 'works' 99.9% exactly as it should ... at least according to what I saw via the Watch Window. *The problem comes on on the 2nd loop, when the InsertValues macros is triggered -- the line 'ActiveCell.value = amount' line results in a blank cell, even though 'amount' is a positive value (as confirmed in Watch Window). So, why would the code insert a blank cell where an available value exists? *In fact, the InsertValues macro wouldn't have been called if 'amount' wasn't a non-blank value in the first place! *The cell formats look OK and no error is produced ... Very odd! TIA, Ray |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|