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
|