Syntax got me again

[DA]

I have the following macro that is supposed to copy one of two areas
depedning upon whehter a certain worksheet cell, whihc is range-named,
is Yes or No. Something in the syntax or dimensioning must be wrong
since it always chooses the 2nd copy range (the else condition), never
the 1st range (the then condition). Perhaps it's how I'm dimensioning
it, perhaps how I am phrasing the if statement, or perhaps you can't
use an IF statement the way I do for just the beginning of a copy
command, or perhaps I need a go to statement.

I'm sure this is trivial to fix, sincw the error is really dumb.
Thanks much!

Please help! Here is my very short macro:

Sub SetUpModelAllocationOnSummarySheet()

Dim ModelsAlreadyBuiltYesNoIndicator As Variant



If ModelsAlreadyBuiltYesNoIndicator = "No" Then
Range("AH20:AH64").Select
Selection.Copy

Else
Range("AG20:AG64").Select
Selection.Copy
End If

Range("Z20:z64").Select

ActiveSheet.Paste
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True _
, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True
End Sub

 

[Wouter HM]

Hi DA,

Your first line of code only creates a variable with a strangs long
name.
At this point it has no value!
In yuor secod lie of code you are testing is the value equals "No".
Which it does not.
So the Else part of your code is executed.

You should asign a value to this variable toget the If part executed
Maybe something like:

ModelsAlreadyBuiltYesNoIndicator = Range("AH1").Value

HTH,

Wouter

 

[DA]

I see - it won't recognize the range name as it stands. Actually, I
only tried that becasue what I was sure would work, didn't.

My whole reason for using the range name was to make sure the macro
worked even if I insert rows or columns so your suggestion isn't quite
good enough (for me). However, somehow, your statement got me to go
back to my first attempt which was:

If Range("ModelsAlreadyBuiltYesNoIndicator").Value = "No" Then

and it works!

I swear that the first time I wrote the macro, that is what I did and
it didn't work, and that is what drove me toward what I sent you. I
don't understand what happened.

In any event, I am fine now, and I thank you.

Dean