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).R ow + 2
1).Address

Can anyone help?

Thanks,
Berni

--
bjwade6
-----------------------------------------------------------------------
bjwade62's Profile: http://www.excelforum.com/member.php...fo&userid=3718
View this thread: http://www.excelforum.com/showthread.php?threadid=56895

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" wrote in message
...

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).R ow + 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).R ow + 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

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" wrote in message
...

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).R ow + 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
...
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" wrote in message
...

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).R ow + 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