View Full Version : ref range to replace sheet name
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
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.
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.