PLEASE help ...

[Ray]

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

 

[John Bundy]

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

 

[Ray]

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

 

[paul.robinson]

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