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

B

bjwade62

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,
Berni
 
B

Bernie Deitrick

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
 
B

bjwade62

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]
 
B

Bernie Deitrick

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 said:
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]
Click to expand...
 
B

Bernie Deitrick

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 said:
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 said:
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]
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

sorting in a macro and defining last row into varible not working in 5
Run-time error when copying entire row! 5
MsgBox including range value 6
Sort by a Variable Range 2
Receiving error 1004 - Method 'Rang' of object '_Global' failed 2
VSTO C# add in Outlook.Appointment error 0
"Run-time error '1004' 5
VBA code for Range 0

Top