Problem using variable name

Scott JS

New Member
Joined
Aug 3, 2005
Messages
33
In attempting to write an VBA marco in excel, both of the two pieces of code below return

Run-time error ‘1004’:
Method “Range” of object “_Global” failed

With the second line of code highlighted.

First attempt

Code:
    finalrow = Range("G65536").End(xlUp).Row
    Range("H & finalrow").Select

Second attempt

Code:
    finalrow = Range("G65536").End(xlUp).Row
    Range("H” & “finalrow").Select

I have "dim'ed" finalrow as an integer. What am I doing wrong?
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Range("H” & finalrow).Select

but you do not need to select a range to work with it.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
I have "dim'ed" finalrow as an integer. What am I doing wrong?
When 'dimming' the data type for rows you want to dim them as a data
type Long instead of Integer.
The data type Integer will only work for numbers (or in your case rows) ranging from
-32,768 through 32,767 whereas the data type Long will work for numbers ranging from
-2,147,483,648 through 2,147,483,647.

Also, to keep your code compatible with the latest (& past/future) version(s) of excel, you
may want to consider not hard coding the last row in the sheet as 65536. The new version
has already exceeded a million rows per sheet so 65536 won’t get you anywhere
near the bottom.
A more flexible syntax would be:
Code:
finalrow = Cells(Rows.Count, "G").End(xlUp).Row
 

Scott JS

New Member
Joined
Aug 3, 2005
Messages
33
Thank you, that helped. But now I can’t dynamically name the range using a variable.

Neither
Code:
    Range("G622:H" & finalrow).Select
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _
        "='sheet'!R622C7:R" & "finalrow" & "C8"
Nor
Code:
    Range("G622:H" & finalrow).Select
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _
        "=’sheet1’!R622C7:R & finalrow & C8"
Ends up naming the range – which I need for subsequent lookups.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Scott

You still aren't using the correct syntax.

Try this.
Code:
Range("G622:H" & finalrow).Name="dupestemp"
Note since there is no worksheet reference this will create the named range on the active sheet.

If you want to specify the sheet:
Code:
Worksheets("Sheet1").Range("G622:H" & finalrow).Name="dupestemp"
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hi,

you were so close, messing up with double quotes
you can use
Code:
Range("G622:H" & finalrow).Select 
    ActiveWorkbook.Names.Add Name:="dupestemp", RefersToR1C1:= _ 
        "='sheet1'!R622C7:R" & finalrow & "C8"
kind regards,
Erik
 

Scott JS

New Member
Joined
Aug 3, 2005
Messages
33

ADVERTISEMENT

Thanks for all your help. My last question is how to code the "hiding of grouped coolumns. what i get when I do it in recorder mode bombs when i try to run it

Code:
    ActiveCell.Range("A1:E1").Select
    ExecuteExcel4Macro "SHOW.DETAIL(2,6,FALSE,,1)"
    ActiveCell.Offset(0, 6).Range("A1:K1").Select
    ExecuteExcel4Macro "SHOW.DETAIL(2,18,FALSE,,7)"

Thanks again for all the help!
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Scott

That's recorded code?

What manual steps did you take?
 

Scott JS

New Member
Joined
Aug 3, 2005
Messages
33
I went to the first column of a grouped section of columns (that were visible), highlighted the 5 columns that need to be hidden, then went to Data -- Group -- Hide detail

Then cursored over to next set of columns that were already grouped (but visible) and went through the same process.

If I just click on the - (minus) sign above the column to the right of the grouped section, the recorder records nothing
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Scott

Could you post some sample data?

The reason I'm curious about this is because of the ExecuteExcel4Macro part of the code.

I don't think I've ever seen that as part of recorded code.
 

Forum statistics

Threads
1,136,650
Messages
5,677,000
Members
419,667
Latest member
MegEri

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top