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.

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"

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.
>
>

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 <Chri...@discussions.microsoft.com>
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.

Thsi is one way

=Myfunction(A110)

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.
>
>

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"
>
>
>