If then else

[Eric]

I need help with an if then else macro

If Sheets("data Worksheet").Range("H26") > 0 Then Range("H26").Select Else
Range("BA1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Test Database").Select
Range("F9").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

What I am looking for is to have H26 on the data worksheet displayed on the
Test database worksheet. But with this macro the H26 cell on the test
database worksheet is being copied on to the same sheet but in a different
place. Why?

Can anyone help?

Eric

 

[JE McGimpsey]

The why is because you don't qualify the "Range("H26")" after "Then"
with the name of the sheet, so

Range("H26").Select

is equivalent to

ActiveSheet.Range("H26").Select

However, you could avoid this problem (and be far more efficient) if you
didn't rely on Selections at all:


With Sheets("data Worksheet").Range("H26")
If .Value > 0 Then _
Sheets("Test Database").Range("F9").Value = .Value
End With

Note the "." before .Value which indicates that the property belongs to
the object in the With statement (i.e., Sheets("data
Worksheet").Range("H9")).

 

[JLGWhiz]

JE said:

Note the "." before .Value which indicates that the property belongs to
the object in the With statement (i.e., Sheets("data
Worksheet").Range("H9")).

But I think he meant:

Note the "." before .Value which indicates that the property belongs to
the object in the With statement (i.e., Sheets("data
Worksheet").Range("H26")).