ExcelBanter - Using a variable in a formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Using a variable in a formula (https://www.excelbanter.com/excel-programming/342955-using-variable-formula.html)

Patrick Simonds

Using a variable in a formula

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:

Bob Phillips[_6_]

Using a variable in a formula

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

JE McGimpsey

Using a variable in a formula

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

In article ,
"Patrick Simonds" wrote:

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:

Patrick Simonds

Using a variable in a formula

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

Patrick Simonds

Using a variable in a formula

I get the same error message

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

In article ,
"Patrick Simonds" wrote:

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:

Tom Ogilvy

Using a variable in a formula

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" wrote in message
...
I get the same error message

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

In article ,
"Patrick Simonds" wrote:

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:

Gary Keramidas

Using a variable in a formula

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

Bob Phillips[_6_]

Using a variable in a formula

Which is the same problem that I found and posted on.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
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" wrote in message
...
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:

All times are GMT +1. The time now is 05:17 PM.