referencing cell address
 

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

referencing cell address
 

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" wrote
in message ...

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

referencing cell address
 

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

referencing cell address
 

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


--
cereldine
------------------------------------------------------------------------
cereldine's Profile: http://www.excelforum.com/member.php...o&userid=32069
View this thread: http://www.excelforum.com/showthread...hreadid=528155

referencing cell address
 

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