Problem with CurrentRegion

[nasar]

I have the following set of data:

A B
20 Day Item
21 1-Nov-02 12
22 1-Dec-02 6
23 2-Dec-03 8

I have named cell A20 rgTransactions.

In my code I wish to do various things within the future growing list around A20. This is my code sample:

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

Private Sub cmdAddDDs_Click()
Dim InsertRow As Integer
Dim rgTX As Range
Dim TXAddress As String

With ActiveSheet
    InsertRow = 0
    Set rgTX = .Range("rgTransactions").CurrentRegion
    InsertRow = rgTX.Rows.Count
    TXAddress = rgTX.Address
End With

End Sub

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Yet when I run this I get the following error:
Run-time error '1004':
Unable to get the CurrentRegion property of the Range class

Any ideas to why this error is occurring would be appreciated.

Regards

Nasar

 

[Loomah]

Nasar
I was unable to reproduce the error you're getting but here are a couple of thoughts
Does the code work if you reference A20 as A20 and not as named range?
Is the active sheet the one containing the data?
Is the spelling of the named range the same in your code as it is in the workbook?

Dunno if any of these will help!

Something else you might want to consider if you get around the error:-
If you data set is going to be BIG, Dim InsertRow as Long not Integer. Integer only copes up to approx 32000 and a bit.

;-)
If a man says something and there are no women there to hear him, is he still wrong?

 

[nasar]

Thanks Loomah,

Just to add, I am running Excel 97.

No change if I include A20, still same error
Active sheet does contain the data (and the command button)
I have double checked the spelling and it matches.

I also notice that if I try and reference other ranges with other attributes (e.g. Value or Formula) I have no problems but if I use CurrentRegion for another range this also gives an error.

Regards

Nasar

 

[Loomah]

nasar
xl97 shouldn't make a dfference but just to be sure I've just checked and I still can't recreate the error you're getting. So, again all I can do is stab in the dark!

All I an think of now is that for some reason the object rgTX isn't being set. Try stepping through the code to see exactly where it falls over. If it looks like this is the problem try the code without setting rgTX ie

With ActiveSheet.Range("rgtransactions").CurrentRegion
    InsertRow = 0
    InsertRow = .Rows.Count
    TXAddress = .Address
    .Select 'loom's line
End With
Debug.Print InsertRow 'loom's line
Debug.Print TXAddress 'loom's line

Other than that I'm stumped, as I say I've managed to run the code every way. This happens to me a lot!!

;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[nasar]

I was invoking the code via a command button on the ActiveSheet. When I move the same code to a sub within a module and invoke it using Run Macro, the code works.

Am I missing something or is it a 'feature'?

 

[xlbo]

Aaaaaaah - you're using the Controls Toolbox CommandButton rather than the forms one aren't you ??
If you are, then right click on it and choose Properties
Set the "Take Focus On Click" property to false

This occurs quite regularly - the button has the focus so any sheet references throw an error unless you use the method above
Rgds
~Geoff~

 

[Loomah]

D'oh
You know he's right (again!)
Cheers mrxlbogeoffmanu man
;-) If a man says something and there are no women there to hear him, is he still wrong?

 

[xlbo]

no worries Loomah ;-)
Tryin' to keep a low profile 'bout the whole Manu ting at the mo....for obvious reasons.....City / Maccabi Haifa.....Nooooooooooooooooooooooooooooooooo
Rgds
~Geoff~