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 |