Problem with Worksheet range.

brooksie

New Member
Joined
Apr 6, 2011
Messages
7
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Maybe

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

brooksie

New Member
Joined
Apr 6, 2011
Messages
7
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
 
Last edited:

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
What is the value of j when this line of code is executed?
 

brooksie

New Member
Joined
Apr 6, 2011
Messages
7

ADVERTISEMENT

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

This now works. thanks for your help.
 

Michael Simpson

Board Regular
Joined
Jun 21, 2010
Messages
232
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)

Code:
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
Code:
Set MyRange = Range(Cells(1, 1), Cells(9, 4))
Is this the only way to do what I'm trying to do ??
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,831
Office Version
  1. 365
Platform
  1. Windows
When you set myRange you don't use any worksheet references for Range or Cells.
 

Michael Simpson

Board Regular
Joined
Jun 21, 2010
Messages
232
Thank you Norie - that did the trick. The correct code should have been

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

Thanks again
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,879
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top