Error 1004 Method 'Range' of object '_Global' failed

I have an Excel spreadsheet that gives me an Error 1004 Method 'Range' of object '_Global' failed at the following line.

startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2, 1).Address

Can anyone help?

Thanks,
Bernie

If startCell is a string, then that should work.

If it is a range object (which is the only way I could get your error) then you need to do this:

Set startCell = Worksheets(worksheetname).Cells(startCell.Row + 2, 1)

HTH,
Bernie
MS Excel MVP


"bjwade62" <[email protected]> wrote in message
news:[email protected]...
>
> I have an Excel spreadsheet that gives me an Error 1004 Method 'Range'
> of object '_Global' failed at the following line.
>
> startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2,
> 1).Address
>
> Can anyone help?
>
> Thanks,
> Bernie
>
>
> --
> bjwade62
> ------------------------------------------------------------------------
> bjwade62's Profile: http://www.excelforum.com/member.php...o&userid=37186
> View this thread: http://www.excelforum.com/showthread...hreadid=568955
>

Thanks Bernie. It is a string and I'm still getting the error. I've pasted the entire Sub below. Thanks for helping me.

Sub CategoryResortAndFormat(worksheetname As String, sortCategoryName As String, sortCategoryCell As String, sortColumn1 As String, sortColumn2 As String, greenBarColumn As String)

Dim sortCategoryNameExpanded As String
Dim i As Long
Dim startcell As String
Dim endCell As String
Dim greenBar As Integer
Dim rowOffset As Integer

' expand sortCategoryName

sortCategoryNameExpanded = sortCategoryName
For i = 1 To (Len(sortCategoryNameExpanded) - 1) * 2 Step 2
sortCategoryNameExpanded = Left(sortCategoryNameExpanded, i) + " " + Mid(sortCategoryNameExpanded, i + 1)
Next i

' begin search for sortCategoryName

For i = 0 To 65000
If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Value = sortCategoryNameExpanded Then
startcell = Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
Exit For
End If
Next i

' continue search looking for "TOTAL " & sortCategoryName

For i = 0 To 65000
If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Value = "TOTAL " & sortCategoryName Then
endCell = Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
Exit For
End If
Next i

' establish the upper left and lower right corners of sort area
' perform resort of category


startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2, 1).Address

endCell = Worksheets(worksheetname).Range(greenBarColumn & CStr(Range(endCell).Row - 1)).Address
Worksheets(worksheetname).Range(startcell & ":" & endCell).Sort _
Key1:=Worksheets(worksheetname).Range(sortColumn1), _
Order1:=xlAscending, _
Key2:=Worksheets(worksheetname).Range(sortColumn2), _
Order2:=xlAscending, _
Header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom

' add green bar to alternating rows - toggling greenBar variable

greenBar = 1
rowOffset = 0
While Range(startcell).Row + rowOffset <= Range(endCell).Row
If greenBar = 1 Then
With Worksheets(worksheetname).Range("A" & CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn & CStr(Range(startcell).Row + rowOffset)).Interior
.ColorIndex = 40
.Pattern = xlSolid
.PatternColorIndex = 2
End With
Else
With Worksheets(worksheetname).Range("A" & CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn & CStr(Range(startcell).Row + rowOffset)).Interior
.ColorIndex = xlNone
End With
End If
rowOffset = rowOffset + 1
greenBar = (greenBar + 1) Mod 2
Wend
End Sub
[/vb]

Bernie,

Yikes(!)

Use built-in methods instead of looping.....

Try this instead - A LOT depends on the values that are passed to the sub: I'm assuming sortColumn1
and sortColumn1 are just column letters, and that the resulting range from the search for the
sortCategoryName includes those columns to the left....

Sub CategoryResortAndFormat( _
worksheetname As String, _
sortCategoryName As String, _
sortCategoryCell As String, _
sortColumn1 As String, _
sortColumn2 As String, _
greenBarColumn As String)

Dim myFC1 As Range
Dim myFC2 As Range

Set myFC1 =
Worksheets(worksheetname).Range(sortCategoryCell).EntireColumn.Find(sortCategoryNameExpanded)
Set myFC2 = Worksheets(worksheetname).Range(sortCategoryCell).EntireColumn.Find("TOTAL " &
sortCategoryName)

If myFC1 Is Nothing Or myFC2 Is Nothing Then
MsgBox "Start or end not found"
Exit Sub
End If

'Whether this works will depend on the value of sortColumn1
With Worksheets(worksheetname)
With .Range(.Cells(myFC1.Row + 2, 1), myFC2)
.Sort Key1:=Worksheets(worksheetname).Range(sortColumn1 & myFC1.Row + 2), _
Order1:=xlAscending, _
Key2:=Worksheets(worksheetname).Range(sortColumn2 & myFC1.Row + 2), _
Order2:=xlAscending, _
Header:=xlNo, _
MatchCase:=False, _
Orientation:=xlTopToBottom
.FormatConditions.Delete
.FormatConditions.Add Type:=xlExpression, _
Formula1:="=MOD(ROW(),2)=0"
.FormatConditions(1).Interior.ColorIndex = 40
End With
End With


HTH,
Bernie
MS Excel MVP


"bjwade62" <[email protected]> wrote in message
news:[email protected]...
>
> Thanks Bernie. It is a string and I'm still getting the error. I've
> pasted the entire Sub below. Thanks for helping me.
>
> Sub CategoryResortAndFormat(worksheetname As String, sortCategoryName
> As String, sortCategoryCell As String, sortColumn1 As String,
> sortColumn2 As String, greenBarColumn As String)
>
> Dim sortCategoryNameExpanded As String
> Dim i As Long
> Dim startcell As String
> Dim endCell As String
> Dim greenBar As Integer
> Dim rowOffset As Integer
>
> ' expand sortCategoryName
>
> sortCategoryNameExpanded = sortCategoryName
> For i = 1 To (Len(sortCategoryNameExpanded) - 1) * 2 Step 2
> sortCategoryNameExpanded = Left(sortCategoryNameExpanded, i) +
> " " + Mid(sortCategoryNameExpanded, i + 1)
> Next i
>
> ' begin search for sortCategoryName
>
> For i = 0 To 65000
> If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i,
> 0).Value = sortCategoryNameExpanded Then
> startcell =
> Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
> Exit For
> End If
> Next i
>
> ' continue search looking for "TOTAL " & sortCategoryName
>
> For i = 0 To 65000
> If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i,
> 0).Value = "TOTAL " & sortCategoryName Then
> endCell =
> Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
> Exit For
> End If
> Next i
>
> ' establish the upper left and lower right corners of sort area
> ' perform resort of category
>
>
> startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2,
> 1).Address
>
> endCell = Worksheets(worksheetname).Range(greenBarColumn &
> CStr(Range(endCell).Row - 1)).Address
> Worksheets(worksheetname).Range(startcell & ":" & endCell).Sort _
> Key1:=Worksheets(worksheetname).Range(sortColumn1), _
> Order1:=xlAscending, _
> Key2:=Worksheets(worksheetname).Range(sortColumn2), _
> Order2:=xlAscending, _
> Header:=xlNo, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom
>
> ' add green bar to alternating rows - toggling greenBar variable
>
> greenBar = 1
> rowOffset = 0
> While Range(startcell).Row + rowOffset <= Range(endCell).Row
> If greenBar = 1 Then
> With Worksheets(worksheetname).Range("A" &
> CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn &
> CStr(Range(startcell).Row + rowOffset)).Interior
> ColorIndex = 40
> Pattern = xlSolid
> PatternColorIndex = 2
> End With
> Else
> With Worksheets(worksheetname).Range("A" &
> CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn &
> CStr(Range(startcell).Row + rowOffset)).Interior
> ColorIndex = xlNone
> End With
> End If
> rowOffset = rowOffset + 1
> greenBar = (greenBar + 1) Mod 2
> Wend
> End Sub
> [/vb]
>
>
> --
> bjwade62
> ------------------------------------------------------------------------
> bjwade62's Profile: http://www.excelforum.com/member.php...o&userid=37186
> View this thread: http://www.excelforum.com/showthread...hreadid=568955
>

Ooops, I forgot to include the code that you had to produce the string sortCategoryNameExpanded -
that part was good...

HTH,
Bernie
MS Excel MVP


"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
news:%23GkWn%[email protected]...
> Bernie,
>
> Yikes(!)
>
> Use built-in methods instead of looping.....
>
> Try this instead - A LOT depends on the values that are passed to the sub: I'm assuming
> sortColumn1 and sortColumn1 are just column letters, and that the resulting range from the search
> for the sortCategoryName includes those columns to the left....
>
> Sub CategoryResortAndFormat( _
> worksheetname As String, _
> sortCategoryName As String, _
> sortCategoryCell As String, _
> sortColumn1 As String, _
> sortColumn2 As String, _
> greenBarColumn As String)
>
> Dim myFC1 As Range
> Dim myFC2 As Range
>
> Set myFC1 =
> Worksheets(worksheetname).Range(sortCategoryCell).EntireColumn.Find(sortCategoryNameExpanded)
> Set myFC2 = Worksheets(worksheetname).Range(sortCategoryCell).EntireColumn.Find("TOTAL " &
> sortCategoryName)
>
> If myFC1 Is Nothing Or myFC2 Is Nothing Then
> MsgBox "Start or end not found"
> Exit Sub
> End If
>
> 'Whether this works will depend on the value of sortColumn1
> With Worksheets(worksheetname)
> With .Range(.Cells(myFC1.Row + 2, 1), myFC2)
> .Sort Key1:=Worksheets(worksheetname).Range(sortColumn1 & myFC1.Row + 2), _
> Order1:=xlAscending, _
> Key2:=Worksheets(worksheetname).Range(sortColumn2 & myFC1.Row + 2), _
> Order2:=xlAscending, _
> Header:=xlNo, _
> MatchCase:=False, _
> Orientation:=xlTopToBottom
> .FormatConditions.Delete
> .FormatConditions.Add Type:=xlExpression, _
> Formula1:="=MOD(ROW(),2)=0"
> .FormatConditions(1).Interior.ColorIndex = 40
> End With
> End With
>
>
> HTH,
> Bernie
> MS Excel MVP
>
>
> "bjwade62" <[email protected]> wrote in message
> news:[email protected]...
>>
>> Thanks Bernie. It is a string and I'm still getting the error. I've
>> pasted the entire Sub below. Thanks for helping me.
>>
>> Sub CategoryResortAndFormat(worksheetname As String, sortCategoryName
>> As String, sortCategoryCell As String, sortColumn1 As String,
>> sortColumn2 As String, greenBarColumn As String)
>>
>> Dim sortCategoryNameExpanded As String
>> Dim i As Long
>> Dim startcell As String
>> Dim endCell As String
>> Dim greenBar As Integer
>> Dim rowOffset As Integer
>>
>> ' expand sortCategoryName
>>
>> sortCategoryNameExpanded = sortCategoryName
>> For i = 1 To (Len(sortCategoryNameExpanded) - 1) * 2 Step 2
>> sortCategoryNameExpanded = Left(sortCategoryNameExpanded, i) +
>> " " + Mid(sortCategoryNameExpanded, i + 1)
>> Next i
>>
>> ' begin search for sortCategoryName
>>
>> For i = 0 To 65000
>> If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i,
>> 0).Value = sortCategoryNameExpanded Then
>> startcell =
>> Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
>> Exit For
>> End If
>> Next i
>>
>> ' continue search looking for "TOTAL " & sortCategoryName
>>
>> For i = 0 To 65000
>> If Worksheets(worksheetname).Range(sortCategoryCell).Offset(i,
>> 0).Value = "TOTAL " & sortCategoryName Then
>> endCell =
>> Worksheets(worksheetname).Range(sortCategoryCell).Offset(i, 0).Address
>> Exit For
>> End If
>> Next i
>>
>> ' establish the upper left and lower right corners of sort area
>> ' perform resort of category
>>
>>
>> startCell = Worksheets(worksheetname).Cells(Range(startCell).Row + 2,
>> 1).Address
>>
>> endCell = Worksheets(worksheetname).Range(greenBarColumn &
>> CStr(Range(endCell).Row - 1)).Address
>> Worksheets(worksheetname).Range(startcell & ":" & endCell).Sort _
>> Key1:=Worksheets(worksheetname).Range(sortColumn1), _
>> Order1:=xlAscending, _
>> Key2:=Worksheets(worksheetname).Range(sortColumn2), _
>> Order2:=xlAscending, _
>> Header:=xlNo, _
>> MatchCase:=False, _
>> Orientation:=xlTopToBottom
>>
>> ' add green bar to alternating rows - toggling greenBar variable
>>
>> greenBar = 1
>> rowOffset = 0
>> While Range(startcell).Row + rowOffset <= Range(endCell).Row
>> If greenBar = 1 Then
>> With Worksheets(worksheetname).Range("A" &
>> CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn &
>> CStr(Range(startcell).Row + rowOffset)).Interior
>> ColorIndex = 40
>> Pattern = xlSolid
>> PatternColorIndex = 2
>> End With
>> Else
>> With Worksheets(worksheetname).Range("A" &
>> CStr(Range(startcell).Row + rowOffset) & ":" & greenBarColumn &
>> CStr(Range(startcell).Row + rowOffset)).Interior
>> ColorIndex = xlNone
>> End With
>> End If
>> rowOffset = rowOffset + 1
>> greenBar = (greenBar + 1) Mod 2
>> Wend
>> End Sub
>> [/vb]
>>
>>
>> --
>> bjwade62
>> ------------------------------------------------------------------------
>> bjwade62's Profile: http://www.excelforum.com/member.php...o&userid=37186
>> View this thread: http://www.excelforum.com/showthread...hreadid=568955
>>
>
>