If then else

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 13 Nov 2007 17:57 GMT

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

Reply to this Message

JE McGimpsey - 13 Nov 2007 18:23 GMT

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")).

> I need help with an if then else macro
>
[quoted text clipped - 15 lines]
>
> Eric

Reply to this Message

JLGWhiz - 13 Nov 2007 19:53 GMT

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")).

> The why is because you don't qualify the "Range("H26")" after "Then"
> with the name of the sheet, so
[quoted text clipped - 36 lines]
> >
> > Eric

Reply to this Message