ActiveCell.Value just disappears .... ?

[Ray]

hi -

I asked this a couple of days ago (see 'PLEASE Help' msg from 22-Feb),
but I wasn't really that clear in what was happening ... so, I'll try
to explain further ... [reduced version of code below]:

The code is designed to check for out-of-sales amounts and copy these
amounts (and the appropriate ancilliary info, ie county_tax) to the
next available row on the Journal sheet. The code appears to run OK
(ie doesn't error out) and, for the most part, does what it's supposed
to. BUT, one line consistently doesn't work properly ...

The first line of the InsertValues code is supposed to enter the
'amount' (a variable assigned in the GetTax code) to the ActiveCell.
Using the Watch Window, I can see that 'amount' holds the correct
value (even when changing macros), but for some reason the value isn't
transferred to the activecell. What would cause this? I've tried
adding additional lines to insert this same value into other sheets,
as a way to check if there was some problem with the value itself ...
but these worked fine. All other variables (ie county_tax) are
inserted correctly.

Any help is greatly appreciated ... this is last piece of an important
project, but being held up by this issue!

TIA,
Ray




Sub GetTax()
Sheets("out-of-state sales").Activate

LineNum = 9
Range("D9").Activate
For LineNum = 9 To 47
If IsEmpty(ActiveCell.Value) = False Then
amount = ActiveCell.Offset(0, 8).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("P75").Activate
Do While IsEmpty(ActiveCell.Value) = False
ActiveCell.Offset(1, 0).Activate
Loop
InsertValues

End If
ActiveCell.Offset(1, 0).Activate
Next LineNum

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

Sheets("Out-of-State Sales").Activate
End Sub

 

[michael.beckinsale]

Hi Ray,

IsEmpty tests the condition of a variable and if l believe what l am
told is said to be unreliable.

Suggest you change to something like;

If ActiveCell.Value <> "" Then

...........rest of code

Regards

Michael

 

[Ray]

Thanks for the suggestion, Michael ... unfortunately, it didn't solve
the issue ...

Any other ideas?