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?
[VBA]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)[/VBA]
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:
[vba]
Address = "'" & Range("A1") & "'!" & Cells(count, 3).Address[/vba]
for ex, if the value in A1 is "MySheet" and count = 5 this code will return
'MySheet'!$C$5
PLS DO NOT PM; OPEN A THREAD INSTEAD!!!
1) Posting Code
[CODE]PasteYourCodeHere[/CODE]
(or paste your code, select it, click # button)
2) Uploading File(s)
Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.
3) Testing the Codes
always back up your files before testing the codes.
4) Marking the Thread as Solved
from Thread Tools (on the top right corner, above the first message)
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,
[vba] p = Worksheets("Master_Agenda").WebBrowser1.Document.parentWindow.execScript("i nitialize()", "", "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("c odeAddress(""" & Address & """, 0)", "", "JavaScript")
Count = Count + 1
Application.Wait (Now + TimeValue("00:00:01"))
Loop[/vba]
You can use the sheet index Sheets(1) or the sheet codename Sheet1 if the name is liable to change.
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
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..
Try
[VBA]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[/VBA]
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Posting Permissions
Reply With Quote