How to use method Range inside IF control structure 1

[qjd2004]

Hi,

I'm trying to Set all the cells in a given range to a value from another cell. I'm using a string variable to change the range whenever I like. It works fine when I assign a value to variable "MyRange" and use it like

Range(MyRange).Select

, except when it's inside an If..Then..Else Statement. When I try to use it here, the VBA editor/compiler says "Method 'Range' of Object_Global Failed"

Here's a sample of my code:

Public Sub SortAll()

Dim ws As Worksheet
Dim LastRow As Long
Set LastCell = Range("A1").SpecialCells(xlCellTypeLastCell)            'gets the last used cell
Dim MyRange As String

Range("J8").Select
    
        'Set up DistID For Sheets with Handling Credit in K8
        If ActiveCell.Value = "Handling Credit" Then
            Range("K8").Select
            ActiveCell.FormulaR1C1 = "DistID"   'Set Column heading
            MyRange = "K9:K" & LastRow                   'Set Range to end at the last row
            Range(MyRange).Select    'IT FAILS HERE
            Selection.Value = "=$H$4"         
            Selection.Copy 
            Selection.PasteSpecial Paste:=xlPasteValues
            Application.CutCopyMode = False
            Selection.NumberFormat = "@"
        Else
            Range("I7").Select
        End If

Can someone please help me by explaining why this doesn't work when inside the IF...Then.....Else directive?

I'm so confused.

PS:I'm not using Option Explicit as I don't know how to define LastCell if I do.

 

[bmoremu]

What is the value of MyRange when the error occurs?

 

[TonyJollans]

Hi qjd2004,

It looks as though you are not setting variable [purple]LastRow[/purple], so it will have a value of zero, and ..

[purple][tt]MyRange = "K9:K" & LastRow [/tt][/purple]

wil set [purple]MyRange[/purple] to "K9:K0", which is an invalid range.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

 

[SkipVought]

qjd2004,

Just a technicality.

"A1" is not a Range. It is a Range Reference or a Range Address.

So my suggestion is to assign a range address to a String variable like
[tt]
MyRngAddr = "A1"
[/tt]
Or a Range
[tt]
Dim MyRange as Range
Set MyRange = Range("A1")
[/tt]


Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[qjd2004]

Hi Guys,

thanks for all your help! You're superstars all of you! It was forgetting to set the variable LastRow that caused the error. I fixed it like this

LastRow = LastCell.Row

and I also used a variable for the range address "A1" which I'll keep in mind in future.

Thanks all of you for your help.