Call back a Dim variable
 

I really feel stupid on this one, but it's 4am here, so maybe you'll cut me
some slack.

Code:
Dim CrntA As String
CrntA = Range("A41").Select
[some stuff]
Range("CrntA").Select

Issue:
I want to return to A41 using the variable, so I can change the variable and
loop the code, but try as I might, my Range.Select method isn't taking me
anywhere.

Error Code: (approximately)
Run-time error '1004'
Method 'Range' of object '_Global' failed

Question:
How do I properly set up a variable, and use it to return to the cell I used
as the variable.

Hope someones out there early on a Sunday. Thanks in advance.

Call back a Dim variable
 

Hi Chris,

maybe like that:

Dim D4 As Range
Set D4 = Range("D4") ' or any other
' do something
D4.Select

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA (not ExcelVBA, for sure)

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

Call back a Dim variable
 

hi
Dim Crnta as range
set Crnta = range("A41")
'some stuff
Crnta.select

works for me. using xp & 03
regards
FSt1

"Chris T-M" wrote:

I really feel stupid on this one, but it's 4am here, so maybe you'll cut me
some slack.

Code:
Dim CrntA As String
CrntA = Range("A41").Select
[some stuff]
Range("CrntA").Select

Issue:
I want to return to A41 using the variable, so I can change the variable and
loop the code, but try as I might, my Range.Select method isn't taking me
anywhere.

Error Code: (approximately)
Run-time error '1004'
Method 'Range' of object '_Global' failed

Question:
How do I properly set up a variable, and use it to return to the cell I used
as the variable.

Hope someones out there early on a Sunday. Thanks in advance.

Call back a Dim variable
 

The problem is the Select method. Select returns "True" not the
Address. Use Address instead. i.e.

Dim strAdd as String
strAdd = Range("A41").Address
' some code
strAdd = Range(str).Select

You could also simply use a Range variable. i.e.

Dim rngVar as Range
Set rngVar = Range("A41")
'some code
rngVar.Select

* note the Set statement for assigning the Range variable

SteveM




On Nov 11, 7:28 am, Chris T-M
wrote:
I really feel stupid on this one, but it's 4am here, so maybe you'll cut me
some slack.

Code:
Dim CrntA As String
CrntA = Range("A41").Select
[some stuff]
Range("CrntA").Select

Issue:
I want to return to A41 using the variable, so I can change the variable and
loop the code, but try as I might, my Range.Select method isn't taking me
anywhere.

Error Code: (approximately)
Run-time error '1004'
Method 'Range' of object '_Global' failed

Question:
How do I properly set up a variable, and use it to return to the cell I used
as the variable.

Hope someones out there early on a Sunday. Thanks in advance.

Call back a Dim variable
 

Thsi is one way

=Myfunction(A1:D10)

Sub MyFunction(Target as Range)

MyFunction = 0
for each cell in Target
MyFunction = MyFunction + cell.value
end sub

"Chris T-M" wrote:

I really feel stupid on this one, but it's 4am here, so maybe you'll cut me
some slack.

Code:
Dim CrntA As String
CrntA = Range("A41").Select
[some stuff]
Range("CrntA").Select

Issue:
I want to return to A41 using the variable, so I can change the variable and
loop the code, but try as I might, my Range.Select method isn't taking me
anywhere.

Error Code: (approximately)
Run-time error '1004'
Method 'Range' of object '_Global' failed

Question:
How do I properly set up a variable, and use it to return to the cell I used
as the variable.

Hope someones out there early on a Sunday. Thanks in advance.

Call back a Dim variable
 

That worked perfectly.

I suppose this issue was just to remind me that the little things count. Oh,
and sleep helps too.

Thank you very much for the quick response.
Chris McCune

"Helmut Weber" wrote:

Hi Chris,

maybe like that:

Dim D4 As Range
Set D4 = Range("D4") ' or any other
' do something
D4.Select

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA (not ExcelVBA, for sure)

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"