Using a variable in a formula

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

Bob Phillips

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)
 
J

JE McGimpsey

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
 
P

Patrick Simonds

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...
 
P

Patrick Simonds

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...
 
T

Tom Ogilvy

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...
 
G

Gary Keramidas

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
 
B

Bob Phillips

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...
 

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

Totally Totally baffled 7
Excel 2013 Merge Data 2
Code problem 1
Macro help needed 4
Using a Variable in a SORT statement 1
VBA: Using a range in a formula 1
Macro Error 3
Refernce sheet names in if statement 4

Top