Using a variable in a formula

  • Thread starter Thread starter Patrick Simonds
  • Start date Start date
P

Patrick Simonds

I get a Method Range of object global failed error on the following line
of code:

If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

On that particular line the variable (rowtop) should be 3 since the
ActiveCell.Row is less than 9.


Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row5 = 8
End If


If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C[" & rowtop & "]").Value > "" Then

Range("A[" & row1 & "]:C[" & row2 & "]").Select
Selection.Sort Key1:=Range("A[" & row1 & "]"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If

Continue1:
 
It the activecell's row is <= 9 then rowtop is NOT set at all, so that is
why it probably fails.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
Look at VBA Help ("Range Property (Application, Range, or Worksheet
Object") - the Range property requires an A1-style reference, which
doesn't include "[" or "]".

So if you're trying to check column C:

If Range("C" & rowtop).Value = Range("A1").Value Then
 
I am not sure I follow.

In this case the active cell was on row 5, less than row 9. If the active
cell had been in say row 10 then the code should have gone to Continue1
(where there is continuing code).


Bob Phillips said:
It the activecell's row is <= 9 then rowtop is NOT set at all, so that is
why it probably fails.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Patrick Simonds said:
I get a Method Range of object global failed error on the following line
of code:

If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

On that particular line the variable (rowtop) should be 3 since the
ActiveCell.Row is less than 9.


Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row5 = 8
End If


If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C[" & rowtop & "]").Value > "" Then

Range("A[" & row1 & "]:C[" & row2 & "]").Select
Selection.Sort Key1:=Range("A[" & row1 & "]"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If

Continue1:
Click to expand...
Click to expand...
 
I get the same error message


JE McGimpsey said:
Look at VBA Help ("Range Property (Application, Range, or Worksheet
Object") - the Range property requires an A1-style reference, which
doesn't include "[" or "]".

So if you're trying to check column C:

If Range("C" & rowtop).Value = Range("A1").Value Then





Patrick Simonds said:
I get a Method Range of object global failed error on the following
line
of code:

If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

On that particular line the variable (rowtop) should be 3 since the
ActiveCell.Row is less than 9.


Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row5 = 8
End If


If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C[" & rowtop & "]").Value > "" Then

Range("A[" & row1 & "]:C[" & row2 & "]").Select
Selection.Sort Key1:=Range("A[" & row1 & "]"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If

Continue1:
Click to expand...
Click to expand...
 
Range fine for me. It sorted rows 4 to 8 on column A.

you initialized row5, but used row2 - another possible problem.

Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

Range("A10").Select
If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row2 = 8
Else
MsgBox "Rowtop, row1, row2 not set, exiting"
Exit Sub
End If


If Range("C" & rowtop).Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C" & rowtop).Value > "" Then
Range("A" & row1 & ":C" & row2).Select
Selection.Sort Key1:=Range("A" & row1), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom


End If

Continue1:

End Sub


--
Regards,
Tom Ogilvy


Patrick Simonds said:
I get the same error message


JE McGimpsey said:
Look at VBA Help ("Range Property (Application, Range, or Worksheet
Object") - the Range property requires an A1-style reference, which
doesn't include "[" or "]".

So if you're trying to check column C:

If Range("C" & rowtop).Value = Range("A1").Value Then





Patrick Simonds said:
I get a Method Range of object global failed error on the following
line
of code:

If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

On that particular line the variable (rowtop) should be 3 since the
ActiveCell.Row is less than 9.


Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row5 = 8
End If


If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C[" & rowtop & "]").Value > "" Then

Range("A[" & row1 & "]:C[" & row2 & "]").Select
Selection.Sort Key1:=Range("A[" & row1 & "]"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If

Continue1:
Click to expand...
Click to expand...
Click to expand...
 
i get it to work with these modifications, but it fails if the activecell
row is less than 9. if it's less than nine, the variable rowtop is assigned
zero, which causes an error.

Option Explicit

Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer
Dim row2 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row2 = 5
row5 = 8
End If


If Range("C" & rowtop).Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C" & rowtop).Value > "" Then

Range("A" & row1 & ":C" & row2).Select
Selection.Sort Key1:=Range("A" & row1), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If
Exit Sub
Continue1:
MsgBox "end"
End Sub
 
Which is the same problem that I found and posted on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

Gary Keramidas said:
i get it to work with these modifications, but it fails if the activecell
row is less than 9. if it's less than nine, the variable rowtop is assigned
zero, which causes an error.

Option Explicit

Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer
Dim row2 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row2 = 5
row5 = 8
End If


If Range("C" & rowtop).Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C" & rowtop).Value > "" Then

Range("A" & row1 & ":C" & row2).Select
Selection.Sort Key1:=Range("A" & row1), Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If
Exit Sub
Continue1:
MsgBox "end"
End Sub


--


Gary


Patrick Simonds said:
I get a Method Range of object global failed error on the following line
of code:

If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

On that particular line the variable (rowtop) should be 3 since the
ActiveCell.Row is less than 9.


Sub aaaData_Sort()

Dim rowtop As Integer
Dim row1 As Integer
Dim row5 As Integer

If ActiveCell.Row > 9 Then
rowtop = 3
row1 = 4
row5 = 8
End If


If Range("C[" & rowtop & "]").Value = Range("A1").Value Then

GoTo Continue1

End If

If Range("C[" & rowtop & "]").Value > "" Then

Range("A[" & row1 & "]:C[" & row2 & "]").Select
Selection.Sort Key1:=Range("A[" & row1 & "]"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End If

Continue1:
Click to expand...
Click to expand...
 
Back
Top