| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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: |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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: |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Variable within a formula | Excel Worksheet Functions | |||
| Using a variable in a formula | Excel Discussion (Misc queries) | |||
| Using a variable in a VBA formula | Excel Discussion (Misc queries) | |||
| Using a variable in formula | Excel Programming | |||
| Formula Variable | Excel Programming | |||
Hybrid Mode
