Excel select and clear range using variable for range name 1

[mike1504]

I am trying to clear a range by selecting it thru a variable. when I get to the line which reads Range (tiledbrange) select I get :
runtime error 1004
Method 'range' of object '_global' failed.

The range is defined in the excel spreadsheet and is of the nature offset(ref,rows,columns,height,width) and is therefore variable in size. I have worked with Excel for 5 years but am new to VB and VBA. How do I determine what is going wrong?

 Public clearrange As String

Sub update_Tile_Data()
    clearrange = "tiledbrange"
        Call Clear_Range(clearrange)
   ....other stuff...
        
        
End Sub


Sub Clear_Range(clearrange)
    Range(tiledbrange).Select ' <--Error occurs here
    Selection.ClearContents
   
End Sub

 

[mike1504]

I posted the error line of code wrong. it should have read

 range(clearrange).select  '<--error occurs here

 

[ADoozer]

the error you get occurs if there is no active worksheet (i think)

try the following code to test

Dim oExcelApp As Excel.Application
Dim oExcelWorkbook As Excel.Workbook

Private Sub Command1_Click()
    
    Set oExcelApp = New Excel.Application
    Set oExcelWorkbook = oExcelApp.Workbooks.Open("c:\test.xls")
    
    oExcelApp.Visible = True
    
    Range("FredTheRange").Select
    Selection.ClearContents
    
    Set oExcelWorkbook = Nothing
    Set oExcelApp = Nothing
    
End Sub

(*NB: needs a reference to microsoft excel x.x object library*)

If somethings hard to do, its not worth doing - Homer Simpson

 

[mike1504]

Thanks Adoozer for your response. I tried to activate the sheet and then the range. I'm still getting the same problem.

I think it has to do with passing "FredTheRange" into the argument list as a string variable. when i use "FredTheRange" it clears the range, but when I set a variable = "FredTheRange" and use the variable I get the error.

By the way the call is made from inside the same excel workbook the range is in.
{realized I was in the wrong place after I posted}



I am able to pass the worksheet name into the sub with a string variable, but it seems to hang on the variable for a range name.

I also found a piece of code in excel help which I tried. I got the same error when I tried to use the variable.

 ' To select a named range, use the GoTo method, which activates the workbook and the worksheet and then selects the range.

Sub ClearRange()
    Application.Goto Reference:="MyBook.xls!MyRange"
    Selection.ClearContents
End Sub
        
The following example shows how the same procedure would be written for the active workbook.

Sub ClearRange()
    Application.Goto Reference:="MyRange"
    Selection.ClearContents
End Sub

The reason for using a variable is I need to call the clear range from about 10 different places.
Thanx

 

[kencat742]

I had this similar problem earlier, In ADoozer's code they have Range("FredTheRange").Select; try adding the worksheet name to it. (i.e. xlWS.Range("FredTheRange").Select) Hope this makes sense

You've heard about the computer programmer that died while washing his hair in the shower. The instructions said, 'Lather, rinse, repeat.'

 

[ADoozer]

ok not sure i follow now.

1) where and how are you naming the range?
2) where are you calling Clear_Range from?
3) where is Clear_Range defined?

(NB: "FredTheRange" is just a random range i set in an excel file called test.xls, unless you use the same name i would expect it to error)

If somethings hard to do, its not worth doing - Homer Simpson

 

[mike1504]

1)The ranges are named in the excel spreadsheet. they are defined with an excel offset(Ref,col,row,Height,width) statement that makes them dynamic, so that the ranges are not always the same size, but each always has a marker for it's top left cell.

2) At one point I am calling clear_range from an open event. The balance of the times will be when someone makes a new selection from a validation list. (There will be 4 validation lists, and each list after the first is defined by the selection on the previous list. )

3)Clear_Range is defined in module 1 which is a part of the workbook.




TIA for your Help

 

[SkipVought]

Hi,

As long as your names are unique this will work. It is possible to have duplicate names in a workbook but not duplicate names in a sheet.

Sub Clear_Range(clearrange)
    Range(tiledbrange).ClearContents
End Sub

The key is, do NOT select.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mike1504]

Thanks Skip. Still I have no good result. I have decide to make 10 different calls. Takes lots less time to program in the long run.

 

[SkipVought]

sorry, looks as if I messed you up...

Sub Clear_Range(clearrange as string)
    Range(clearrange).ClearContents
End Sub

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mike1504]

Thanks Skip, but I had aleady caught the variable. It still gave the 1004 _global... error. When I have time to play, I may set up a sheet to just experiment on this issue. I think I saw it work on one worksheet (called from the open workbook event) then when I called the sub from another place with another string value attached to the variable (another sheet in the same book) I got the error. since then I get the error with the first call as well.

 

[johnwm]

Exactly how are you setting your Range? Are you using RefersTo?

________________________________________________________________
If you want to get the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller

 

[mike1504]

The ranges are defined in the excel workbook as follows:

branddbrange	=OFFSET(tiledb,0,0,COUNTA('Tile Data'!$B$3:E$1003))
brandvalrange	=OFFSET(extractbrand,0,0,COUNTA('Tile Data'!$B$1014:$B$2014),1)
classvalrange	=OFFSET(extractclass,0,0,COUNTA('Tile Data'!$D$1014:$D$2014),1)
colorvalrange	=OFFSET(extractcolor,0,0,COUNTA('Validation Lists'!$E$1014:$E$2014),1)
stylevalrange	=OFFSET(extractstyle,0,0,COUNTA('Tile Data'!$C$1014:$C$2014),1)

They are dynamic. I opened the Insert name dialog box and defined them there.


TYVM

 

[SkipVought]

How are tiledb, extractbrand, extractclass, extractcolor, extractstyle defined?

Does 'Tile Data'!$B$3:E$1003 have a datavalue in EVERY CELL in that range?

Does 'Tile Data'!$B$1014:$B$2014 has a datavalue in EVERY CELL in that range?

same for C, D, & E?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mike1504]

The markers for the start of each range are defined by a fixed reference to a single cell as follows:

extractbrand	='Tile Data'!$B$1014
extractclass	='Tile Data'!$D$1014
extractcolor	='Tile Data'!$E$1014
extractstyle	='Tile Data'!$C$1014 
tiledb	='Tile Data'!$B$3

The ranges are defined by the offset function, and the 1000 cells are set so that the range can expand and contract up to 1000 entries without changing the formula. None of the ranges go to the full 1000 item limit.
currently the datatbase has about 226 entries in it vertically. It is about 12 entries wide. the brandvalrange has 5 entries in it. When it is functioning properly the other validation ranges will have from 1 to 25 items in them...until the db expands.

The purpose behind the scheme it to make 4 validation lists derived from a database. The choice in each list will determine the choices available in the next.


I worked this out in excel without vba, but it was so bulky that the calculations took as much as 30 seconds to complete, and if you made a change in the 2nd validation list prior to completing calculations for the first.... well the sheet got confused.

 

[SkipVought]

You have a problem:
[tt]
branddbrange =OFFSET(tiledb,0,0,COUNTA('Tile Data'!$B$3:E$1003))
[/tt]
1. there is no 5[sup]th[/sup] argument for the number of columns in your range.

2. the row count is 1000 * 4. that's not right, is it?

3. the row count and column count (args 4 & 5) in your case must equal 1000 & 4 respectively -- I'm assuming that you meant it to be a matrix array.

4. I don't understand why your othere COUNTA function are referencing other sheets. ALL you want is a value that returns the count of rows in your table. THEY ARE ALL THE SAME COUNT! the ONLY thing that changes is the ANCHOR REFERENCE



Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[mike1504]

Thank you Skip. When I went back and started over. I started with fewer subs and names. I got the range name to transfer by variable. Appears I did have quite a bit of typo's in the last spread sheet. Just not good at seeing them yet in VB. The spreadsheet makes it pretty easy to see them.