Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
I am trying to call to a second workbook, to a specific sheet, and a specific
range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
I think I'd do something like:
Option Explicit Sub testme() Dim ShtRef As String Dim myAddr As String myAddr = "f5:Z5" ShtRef = "" Select Case LCase(Workbooks("book1.xls") _ .Worksheets("sheet1").Range("b2").Value) Case Is = "rel 1a": ShtRef = "rel 1a" Case Is = "cis release 1b.published": ShtRef = "rel 1b" Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr" Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated" End Select If ShtRef = "" Then 'do nothing Else On Error Resume Next Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(myAddr) If Err.Number < 0 Then MsgBox "Invalid sheet name!" Err.Clear End If On Error GoTo 0 End If End Sub ======== I think it's a good idea to fully qualify your range--like in this line: Select case lcase(workbooks("book1.xls") _ .worksheets("sheet1").range("b2").value) And application.goto works so that you don't have to activate a workbook, select a sheet and then select the range. (Watch out for typos. This compiled ok, but I didn't recreate the test workbooks.) Jeff wrote: I am trying to call to a second workbook, to a specific sheet, and a specific range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
Dave thanks so much for the help on this. I have another question which is,
how do I construct a formula with this new information, ie pathname. I have tried the following to no avail; xlApp.Range("g27").Formula = "=[""Release Plan (1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)" and "='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month" And "=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)" But I seem to have the syntax all wrong for some reason. HELP! Thanks Jeff "Dave Peterson" wrote: I think I'd do something like: Option Explicit Sub testme() Dim ShtRef As String Dim myAddr As String myAddr = "f5:Z5" ShtRef = "" Select Case LCase(Workbooks("book1.xls") _ .Worksheets("sheet1").Range("b2").Value) Case Is = "rel 1a": ShtRef = "rel 1a" Case Is = "cis release 1b.published": ShtRef = "rel 1b" Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr" Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated" End Select If ShtRef = "" Then 'do nothing Else On Error Resume Next Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(myAddr) If Err.Number < 0 Then MsgBox "Invalid sheet name!" Err.Clear End If On Error GoTo 0 End If End Sub ======== I think it's a good idea to fully qualify your range--like in this line: Select case lcase(workbooks("book1.xls") _ .worksheets("sheet1").range("b2").value) And application.goto works so that you don't have to activate a workbook, select a sheet and then select the range. (Watch out for typos. This compiled ok, but I didn't recreate the test workbooks.) Jeff wrote: I am trying to call to a second workbook, to a specific sheet, and a specific range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
I'd let excel figure things out:
dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(Plan_month) 'Plan_month is a variable or a real range name? 'if it's a range name (insert|name), then dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range("Plan_month") Then later... workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.add(external:=true) ====== I think I'd be more specific than xlApp.range("g27"), too. Jeff wrote: Dave thanks so much for the help on this. I have another question which is, how do I construct a formula with this new information, ie pathname. I have tried the following to no avail; xlApp.Range("g27").Formula = "=[""Release Plan (1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)" and "='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month" And "=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)" But I seem to have the syntax all wrong for some reason. HELP! Thanks Jeff "Dave Peterson" wrote: I think I'd do something like: Option Explicit Sub testme() Dim ShtRef As String Dim myAddr As String myAddr = "f5:Z5" ShtRef = "" Select Case LCase(Workbooks("book1.xls") _ .Worksheets("sheet1").Range("b2").Value) Case Is = "rel 1a": ShtRef = "rel 1a" Case Is = "cis release 1b.published": ShtRef = "rel 1b" Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr" Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated" End Select If ShtRef = "" Then 'do nothing Else On Error Resume Next Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(myAddr) If Err.Number < 0 Then MsgBox "Invalid sheet name!" Err.Clear End If On Error GoTo 0 End If End Sub ======== I think it's a good idea to fully qualify your range--like in this line: Select case lcase(workbooks("book1.xls") _ .worksheets("sheet1").range("b2").value) And application.goto works so that you don't have to activate a workbook, select a sheet and then select the range. (Watch out for typos. This compiled ok, but I didn't recreate the test workbooks.) Jeff wrote: I am trying to call to a second workbook, to a specific sheet, and a specific range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _
= "=" & myrng.add(external:=true) do you mean? workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.address(external:=true) -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... I'd let excel figure things out: dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(Plan_month) 'Plan_month is a variable or a real range name? 'if it's a range name (insert|name), then dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range("Plan_month") Then later... workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.add(external:=true) ====== I think I'd be more specific than xlApp.range("g27"), too. Jeff wrote: Dave thanks so much for the help on this. I have another question which is, how do I construct a formula with this new information, ie pathname. I have tried the following to no avail; xlApp.Range("g27").Formula = "=[""Release Plan (1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)" and "='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month" And "=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)" But I seem to have the syntax all wrong for some reason. HELP! Thanks Jeff "Dave Peterson" wrote: I think I'd do something like: Option Explicit Sub testme() Dim ShtRef As String Dim myAddr As String myAddr = "f5:Z5" ShtRef = "" Select Case LCase(Workbooks("book1.xls") _ .Worksheets("sheet1").Range("b2").Value) Case Is = "rel 1a": ShtRef = "rel 1a" Case Is = "cis release 1b.published": ShtRef = "rel 1b" Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr" Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated" End Select If ShtRef = "" Then 'do nothing Else On Error Resume Next Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(myAddr) If Err.Number < 0 Then MsgBox "Invalid sheet name!" Err.Clear End If On Error GoTo 0 End If End Sub ======== I think it's a good idea to fully qualify your range--like in this line: Select case lcase(workbooks("book1.xls") _ .worksheets("sheet1").range("b2").value) And application.goto works so that you don't have to activate a workbook, select a sheet and then select the range. (Watch out for typos. This compiled ok, but I didn't recreate the test workbooks.) Jeff wrote: I am trying to call to a second workbook, to a specific sheet, and a specific range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with Sheet references
Ress(T) assured that I did.
Thanks for the correction. Tom Ogilvy wrote: workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.add(external:=true) do you mean? workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.address(external:=true) -- Regards, Tom Ogilvy "Dave Peterson" wrote in message ... I'd let excel figure things out: dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(Plan_month) 'Plan_month is a variable or a real range name? 'if it's a range name (insert|name), then dim myRng as range set myrng = Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range("Plan_month") Then later... workbooks("book1.xls").worksheets("sheet1").range( "g27").formula _ = "=" & myrng.add(external:=true) ====== I think I'd be more specific than xlApp.range("g27"), too. Jeff wrote: Dave thanks so much for the help on this. I have another question which is, how do I construct a formula with this new information, ie pathname. I have tried the following to no avail; xlApp.Range("g27").Formula = "=[""Release Plan (1,2,3,4).xls""].Worksheets(ShtRef).Range(Plan_Month)" and "='[Release Plan (1,2,3,4).xls]Rel 1a'!Plan_Month" And "=""[Release Plan (1,2,3,4).xls]"" ShtRef & ""!"" & (Plan_Month)" But I seem to have the syntax all wrong for some reason. HELP! Thanks Jeff "Dave Peterson" wrote: I think I'd do something like: Option Explicit Sub testme() Dim ShtRef As String Dim myAddr As String myAddr = "f5:Z5" ShtRef = "" Select Case LCase(Workbooks("book1.xls") _ .Worksheets("sheet1").Range("b2").Value) Case Is = "rel 1a": ShtRef = "rel 1a" Case Is = "cis release 1b.published": ShtRef = "rel 1b" Case Is = "cis release 2_ivr": ShtRef = "rel 2 - ivr" Case Is = "cis release 3 - development.mpp": ShtRef = "rel 3 estimated" End Select If ShtRef = "" Then 'do nothing Else On Error Resume Next Application.Goto Workbooks("Release Plan (1,2,3,4).xls") _ .Worksheets(ShtRef).Range(myAddr) If Err.Number < 0 Then MsgBox "Invalid sheet name!" Err.Clear End If On Error GoTo 0 End If End Sub ======== I think it's a good idea to fully qualify your range--like in this line: Select case lcase(workbooks("book1.xls") _ .worksheets("sheet1").range("b2").value) And application.goto works so that you don't have to activate a workbook, select a sheet and then select the range. (Watch out for typos. This compiled ok, but I didn't recreate the test workbooks.) Jeff wrote: I am trying to call to a second workbook, to a specific sheet, and a specific range. The Hook here is that I want to select sheets based upon my if then statements such that if cell B2 = "Rel 1a", then the code would jump to Workbook "Release Plan (1,2,3,4).xls") and to Sheet = "Rel 1a" and select the range =Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)). I cannot for the life of figure out how to assemble the string. Could someone please point me in th eright direction please. Jeff Dim Max As String Dim Xlmonth As String Dim ShtRef As String Dim xlrange As Excel.Range Dim Project As Object Dim Page As Object Dim Totals As Object If xlApp.Range("B2") = "Rel 1a" Then ShtRef = "Rel 1a" If xlApp.Range("B2") = "CIS Release 1B.Published" Then ShtRef = "Rel 1b" If xlApp.Range("B2") = "CIS Release 2_IVR" Then ShtRef = "Rel 2 - IVR" If xlApp.Range("B2") = "CIS Release 3 - Development.mpp" Then ShtRef = "Rel 3 Estimated" Project = xlApp.Windows("Release Plan (1,2,3,4).xls") Page = xlApp.Worksheets(ShtRef) Totals = xlApp.Range(xlApp.Cells(5, 6), xlApp.Cells(5, 26)) xlrange = (Project & Page & Totals) xlrange.Select -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cell references not working | Excel Discussion (Misc queries) | |||
Cell references stopped working | Excel Discussion (Misc queries) | |||
working with references of cells | Excel Worksheet Functions | |||
External References not working right. | Excel Worksheet Functions | |||
references between macros not working | Excel Programming |