Problem with Worksheet range.

[brooksie]

Hi all, I'm new to VBA and have been working through the livelessons DVD; which has been a great help.. but i have run into a problem which im not sure whats going on.

i have the below line of code:

WSSA.Range(Cells(j, 1), Cells(j, 10)).Interior.ColorIndex = 3

Sometimes when i run the macro it works. but other times i get an error

Runtime 1004 - Method 'Range' of object '_Worksheet' Failed.

I have the below code declared at the top..
Dim WSSA As Worksheet
Set WSSA = Worksheets("Server AppManagers")

Thanks

 

[VoG]

Maybe

WSSA.Range(WSSA.Cells(j, 1), WSSA.Cells(j, 10)).Interior.ColorIndex = 3

 

[brooksie]

Thanks for the quick response, still dosent work.

seems to be something to do with the range() as when i just want to color 1 cell it works every time :$

--update--

Ok i found out why its working part of the time..

if im in Server AppManagers sheet it works every time.. if im in another sheet it fails. guess i've messed up the coding somewhere

 

[VoG]

What is the value of j when this line of code is executed?

 

[brooksie]

Sorry confused myself and missed one WSSA. before the cells.

This now works. thanks for your help.

 

[Michael Simpson]

I'm going to take the freedom of appending to this topic with a similar question (moderator - please move to new topic if inappropriate).

My problem is very similar to Brooksie's inasmuch as it seems to work sporadically. Here's my code (I'm looking for cells containing 1 & 31 on sheet 4 in column A)

Dim i As Integer, counter As Integer
    Dim WS As Worksheet, rngFind, rngRow As Range
    Dim cell As Range, test As Range, myrange As Range
    Dim temp As String
    Dim start_row As Variant, end_row As Variant
    '
    ' Redim the public variabeles to be 31 "days"
    ReDim intern_ranta_underskott(31)
    ReDim intern_ranta_overskott(31)

    Sheets(4).Activate

    ' Remember - this type of code DEMANDS that you use the sheet name rather than number
    Set WS = ThisWorkbook.Sheets("Internränta")
    
    '   Look for the first occurrence of a 1 in column A (ie, day 1)
     Set rngFind = WS.Range("A:A").Find(what:="1", MatchCase:=True)
    '   By default
    sw_error = False

    If Not rngFind Is Nothing Then
        ' Found the day
        Set test = rngFind
        start_row = test.row
    Else
        ' Serious error
        MsgBox "Serious error - couldn't find a 1 in column A in sheet 4", vbCritical
        sw_error = True
        Exit Sub
    End If

    Set rngFind = WS.Range("A:A").Find(what:="31", MatchCase:=True)

    If Not rngFind Is Nothing Then
        ' Found the day
        Set test = rngFind
        end_row = test.row
    Else
        ' serious error
        MsgBox "Serious error - couldn't find a 31 in column A in sheet 4", vbCritical
        sw_error = True
        Exit Sub
    End If
        
[B][COLOR=Red]    Set myrange = Range("A" & start_row & ":A" & end_row)[/COLOR][/B]
    ' Loop round filling the two arrays
    i = 1
     For Each cell In myrange
    'For counter = start_row To end_row
        intern_ranta_underskott(i) = cell.Offset(0, 1).Value
        intern_ranta_overskott(i) = cell.Offset(0, 2).Value
        i = i + 1
    Next cell

The line marked in red fails with 1004. start_row and end_row both have what seem to be valid values (5 & 35).

I've seen LOADS of examples where the range spec is something like "A1:Z100", but the only ones where variables seem to be used seems to be similar to

Set MyRange = Range(Cells(1, 1), Cells(9, 4))

Is this the only way to do what I'm trying to do ??

 

[Norie]

When you set myRange you don't use any worksheet references for Range or Cells.

 

[Michael Simpson]

Thank you Norie - that did the trick. The correct code should have been

Set myrange = [COLOR=Red][B]WS.[/B][/COLOR]Range("A" & start_row & ":A" & end_row)

Thanks again