PDA

View Full Version : ref range to replace sheet name



wilg
03-29-2011, 09:50 PM
I have this piece of code. What I would like is to refer to the name of the sheet which is in range a1. I'v tried

worksheets.range("a1").cells(count,3) etc....

but it does not work. Any suggestions to have the sheet name refer to range a1 as it's name?

Address = Worksheets("Master_Agenda").Cells(Count, 3) & ", " & Worksheets("Master_Agenda").Cells(Count, 6) & ", " & Worksheets("Master_Agenda").Cells(Count, 7) & ", " & Worksheets("Master_Agenda").Cells(Count, 8)

mancubus
03-29-2011, 10:43 PM
i'm not sure if i got it but if you are wanting to return a worksheet name from A1 with cell reference from Cells(count, 3) then try:


Address = "'" & Range("A1") & "'!" & Cells(count, 3).Address

for ex, if the value in A1 is "MySheet" and count = 5 this code will return

'MySheet'!$C$5

wilg
03-30-2011, 01:22 PM
Im actually trying ro replace ("MASTER_AGENDA") with a referance to the sheets name. If I change the sheets name, I need "MASTER_AGENDA" to change.

I could not get your code to work. I've included anoth piece of code it it helps,

p = Worksheets("Master_Agenda").WebBrowser1.Document.parentWindow.execScript("initialize()", "", "JavaScript")

Count = 16 'line of address
Do While Len(Worksheets("Master_Agenda").Cells(Count, 3))
Address = Worksheets("Master_Agenda").Range("A14").Text & " " & Worksheets("Master_Agenda").Cells(Count, 2) & "- " & Worksheets("Master_Agenda").Cells(Count, 3) & ", " & Worksheets("Master_Agenda").Cells(Count, 6) & ", " & Worksheets("Master_Agenda").Cells(Count, 7) & ", " & Worksheets("Master_Agenda").Cells(Count, 8)
CommandButton1.Caption = "BUILDING " & Address

p = Worksheets("Master_Agenda").WebBrowser1.Document.parentWindow.execScript("codeAddress(""" & Address & """, 0)", "", "JavaScript")
Count = Count + 1

Application.Wait (Now + TimeValue("00:00:01"))
Loop

mdmackillop
03-30-2011, 02:24 PM
You can use the sheet index Sheets(1) or the sheet codename Sheet1 if the name is liable to change.

wilg
03-30-2011, 03:27 PM
Would that then be...


Address = Worksheets(sheets1).Range("A14").Text &...etc

or

Address = Worksheets("Sheets1").Range("A14").Text &...etc

or other...can't seem to get it goin..

mdmackillop
03-30-2011, 03:35 PM
Try
With Sheet1
Do While Len(.Cells(Count, 3))
Address = .Range("A14").Text & " " & .Cells(Count, 2) & "- " & _
.Cells(Count, 3) & ", " & .Cells(Count, 6) & ", " & _
.Cells(Count, 7) & ", " & .Cells(Count, 8)

CommandButton1.Caption = "BUILDING " & Address

'p = .WebBrowser1.Document.parentWindow.execScript("codeAddress(""" & Address & """, 0)", "", "JavaScript")
Count = Count + 1

Application.Wait (Now + TimeValue("00:00:01"))
Loop
End With