Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
Referencing a cell address in the Offset formla | Excel Worksheet Functions | |||
Referencing the same cell address while copying a formula | Excel Worksheet Functions | |||
referencing a checkbox cell address in a worksheet | Excel Programming |