referencing cell address

[cereldine]

Could somene point me in the right direction with the below code, this particular code is going to be part of a larger procedure but i would like to understand this first.

The problem i'm having is i don't know syntax to reference a cell address,
meaning that the range variable gets set to the text value of A1 instead of A1 itself, this messes up the rest of the statement. The bit i would like some help on is in green
thanks

Public Sub export()
Dim rng As Range
Dim temprng As Range
Dim rngName As String

Range("A1").Select

Set rng = ActiveCell
do
rngName = rng.Text


MsgBox "Filepath " & rngText

temprng = Cells(rng).Offset(1, 0).Row
rng = temprng
Loop Until rngName = ""

End Sub

[Zack Barresse]

Hi there,

Change this ...

Cells(rng).

... to this ..

rng.

As you set 'rng' as a range object, you use it as such. The way you have it
you're trying to pass 'rng' as an argument property instead of an object.

HTH

--
Regards,
Zack Barresse, aka firefytr
To email, remove NOSPAM


"cereldine" <[email protected]> wrote
in message news:[email protected]...
>
> Could somene point me in the right direction with the below code, this
> particular code is going to be part of a larger procedure but i would
> like to understand this first.
>
> The problem i'm having is i don't know syntax to reference a cell
> address,
> meaning that the range variable gets set to the text value of A1
> instead of A1 itself, this messes up the rest of the statement. The bit
> i would like some help on is in green
> thanks
>
> Public Sub export()
> Dim rng As Range
> Dim temprng As Range
> Dim rngName As String
>
> Range("A1").Select
>
> Set rng = ActiveCell
> do
> rngName = rng.Text
>
>
> MsgBox "Filepath " & rngText
>
> temprng = Cells(rng).Offset(1, 0).Row
> rng = temprng
> Loop Until rngName = ""
>
> End Sub
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile:
> http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=528155
>

[visdev1]

yes zack is correct but you also need to put in a SEt and remove Row from the
end of that line of code. Also a minor mistake in your msgbox, rngText
should be rng.Text


this is how it should look

Range("A1").Select

Set rng = ActiveCell
Do
rngName = rng.Text


MsgBox "Filepath " & rng.Text

Set temprng = rng.Offset(1, 0)
rng = temprng
Loop Until rngName = ""


"cereldine" wrote:

>
> Could somene point me in the right direction with the below code, this
> particular code is going to be part of a larger procedure but i would
> like to understand this first.
>
> The problem i'm having is i don't know syntax to reference a cell
> address,
> meaning that the range variable gets set to the text value of A1
> instead of A1 itself, this messes up the rest of the statement. The bit
> i would like some help on is in green
> thanks
>
> Public Sub export()
> Dim rng As Range
> Dim temprng As Range
> Dim rngName As String
>
> Range("A1").Select
>
> Set rng = ActiveCell
> do
> rngName = rng.Text
>
>
> MsgBox "Filepath " & rngText
>
> temprng = Cells(rng).Offset(1, 0).Row
> rng = temprng
> Loop Until rngName = ""
>
> End Sub
>
>
> --
> cereldine
> ------------------------------------------------------------------------
> cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
> View this thread: http://www.excelforum.com/showthread...hreadid=528155
>
>

[cereldine]

Thanks, this set me on right track although i had to change last part to this to avoid endless loop

Set rng = rng.Offset(1, 0)

Loop Until rng = ""

Another question im using this to now open another workbook,

If MsgBox("Use This Filepath " & rngName & " ?", vbYesNo) = vbYes Then
Workbooks.Open (rngName)

How can i ammend this to point at a specific workSHEET in question?

Woulkd something like Workbooks.Open (rngName)&"Sheet2" work?

Also finally does the docmd functionality work in excel? This is something ive used alot in Access vba

[[email protected]]

Hi
Workbooks.open(rngName) 'now active
ActiveWorkbook.Worksheets.Sheets2.Activate
or
ActiveWorkbook.Worksheets("Sheet2").Activate
or
ActiveWorkbook.Worksheets(2).Activate

don't know about docmd sorry

regards
Paul