Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
Hi, I have created with the very much appreciated help from these discussion
boards a workbook that includes a tab that has a high level summary price based on users input and then a hyperlink that once clicked on will break out the prices based on their choices at a more detailed level I'm struggling with 3 things 1) Getting the project price to move over to column E it is currently in Column C 2) Adding a Total Addt'l products price between the new rows and the Project Price 3) Is there a way to incorporate all of this data into a Word letter when it's all done? Here's the code Sub CreateTab1() Dim ws As Worksheet Set ws = gettab("tab1") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("ProjectModel.price") End Sub Sub CreateTab2() Dim ws As Worksheet Set ws = gettab("tab2") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("TermModel.price") End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
I really don't like your programming style. it is hard to follow. I rewrote
youcode below. It is much easier to understand like the changes I made below. You should easily be able to change to column E with the code below. The problem with your orignal code is something the old column d is being put in column e on the new sheet. to get an addition row on the target sheet you have to increment Targetrow twice in the code below --------------------------------------------------------------------------------------- sample of how to add an additional row. TargetRow is incremented twice. Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 'put the subtotals in here TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 -------------------------------------------------------------------------------------------- Sub CreateTab1() Dim Targetws As Worksheet Set Targetws = gettab("tab1") Set Sourcews = Worksheets("Addtl Products") With Targetws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") TargetRow = 4 End With with Sourcews SourceRowCount = 12 Do while .Range("C" SourcerowCount) < "" Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 loop end with Targetws.Range("B" & Targetrow) = "Project Price" Set rPart = Worksheets("Addtl Products").Range("C12") End Sub "Heather" wrote: Hi, I have created with the very much appreciated help from these discussion boards a workbook that includes a tab that has a high level summary price based on users input and then a hyperlink that once clicked on will break out the prices based on their choices at a more detailed level I'm struggling with 3 things 1) Getting the project price to move over to column E it is currently in Column C 2) Adding a Total Addt'l products price between the new rows and the Project Price 3) Is there a way to incorporate all of this data into a Word letter when it's all done? Here's the code Sub CreateTab1() Dim ws As Worksheet Set ws = gettab("tab1") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("ProjectModel.price") End Sub Sub CreateTab2() Dim ws As Worksheet Set ws = gettab("tab2") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("TermModel.price") End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
Thank you, but I'm getting syntax errors on this line
Do while .Range("C" SourcerowCount) < "" "Joel" wrote: I really don't like your programming style. it is hard to follow. I rewrote youcode below. It is much easier to understand like the changes I made below. You should easily be able to change to column E with the code below. The problem with your orignal code is something the old column d is being put in column e on the new sheet. to get an addition row on the target sheet you have to increment Targetrow twice in the code below --------------------------------------------------------------------------------------- sample of how to add an additional row. TargetRow is incremented twice. Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 'put the subtotals in here TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 -------------------------------------------------------------------------------------------- Sub CreateTab1() Dim Targetws As Worksheet Set Targetws = gettab("tab1") Set Sourcews = Worksheets("Addtl Products") With Targetws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") TargetRow = 4 End With with Sourcews SourceRowCount = 12 Do while .Range("C" SourcerowCount) < "" Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 loop end with Targetws.Range("B" & Targetrow) = "Project Price" Set rPart = Worksheets("Addtl Products").Range("C12") End Sub "Heather" wrote: Hi, I have created with the very much appreciated help from these discussion boards a workbook that includes a tab that has a high level summary price based on users input and then a hyperlink that once clicked on will break out the prices based on their choices at a more detailed level I'm struggling with 3 things 1) Getting the project price to move over to column E it is currently in Column C 2) Adding a Total Addt'l products price between the new rows and the Project Price 3) Is there a way to incorporate all of this data into a Word letter when it's all done? Here's the code Sub CreateTab1() Dim ws As Worksheet Set ws = gettab("tab1") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("ProjectModel.price") End Sub Sub CreateTab2() Dim ws As Worksheet Set ws = gettab("tab2") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("TermModel.price") End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
Typo alert, the line should read:
Do while .Range("C" & SourceRowCount) < "" Regards, Per On 20 Aug., 04:45, Heather wrote: Thank you, but I'm getting syntax errors on this line Do while .Range("C" SourcerowCount) < "" "Joel" wrote: I really don't like your programming style. *it is hard to follow. *I rewrote youcode below. *It is much easier to understand like the changes I made below. *You should easily be able to change to column E with the code below. * The problem with your orignal code is something the old column d is being put in column e on the new sheet. to get an addition row on the target sheet you have to increment Targetrow twice in the code below ---------------------------------------------------------------------------*------------ sample of how to add an additional row. *TargetRow is incremented twice. * * * * * Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value * * * * * * * Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value * * * * * Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value * * * * * TargetRow = Targetrow + 1 * * * * * 'put the subtotals in here * * * * * TargetRow = Targetrow + 1 * * * * * SourceRowCount = SourceRowCount + 1 ---------------------------------------------------------------------------*----------------- Sub CreateTab1() * * Dim Targetws As Worksheet * * Set Targetws = gettab("tab1") * * Set Sourcews = Worksheets("Addtl Products") * * With Targetws * * * * .Range("B3") = "Developers" * * * * .Range("C3") = Worksheets("Planner").Range("B5") * * * * TargetRow = 4 * * End With * * with Sourcews * * * *SourceRowCount = 12 * * * *Do while .Range("C" SourcerowCount) < "" * * * * * Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value * * * * * * * Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value * * * * * Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value * * * * * TargetRow = Targetrow + 1 * * * * * SourceRowCount = SourceRowCount + 1 * * * *loop * * end with * * Targetws.Range("B" & Targetrow) = "Project Price" * * * * Set rPart = Worksheets("Addtl Products").Range("C12") End Sub "Heather" wrote: Hi, *I have created with the very much appreciated help from these discussion boards a workbook that includes a tab that has a high level summary price based on users input and then a hyperlink that once clicked on will break out the prices based on their choices at a more detailed level I'm struggling with 3 things 1) *Getting the project price to move over to column E it is currently in Column C 2) *Adding a Total Addt'l products price between the new rows and the Project Price 3) Is there a way to incorporate all of this data into a Word letter when it's all done? Here's the code Sub CreateTab1() * * Dim ws As Worksheet * * Set ws = gettab("tab1") * * Dim rPart As Range * * Dim Target As Range * * With ws * * * * .Range("B3") = "Developers" * * * * .Range("C3") = Worksheets("Planner").Range("B5") * * * * Set Target = .Range("C4") * * End With * * * * Set rPart = Worksheets("Addtl Products").Range("C12") * * * * Do Until rPart = "" * * * * * * Target = rPart.Value * * * * * * Target.Offset(, 1) = rPart.Offset(, -1).Value * * * * * * Target.Offset(, 2) = rPart.Offset(, 1) * * * * * * Set Target = Target.Offset(1) * * * * * * Set rPart = rPart.Offset(1) * * * * Loop * * * * Target.Offset(, -1) = "Project Price" * * * * Target = Worksheets("Planner").Range("ProjectModel.price") End Sub Sub CreateTab2() * * Dim ws As Worksheet * * Set ws = gettab("tab2") * * Dim rPart As Range * * Dim Target As Range * * With ws * * * * .Range("B3") = "Developers" * * * * .Range("C3") = Worksheets("Planner").Range("B5") * * * * Set Target = .Range("C4") * * End With * * * * Set rPart = Worksheets("Addtl Products").Range("C12") * * * * Do Until rPart = "" * * * * * * Target = rPart.Value * * * * * * Target.Offset(, 1) = rPart.Offset(, -1).Value * * * * * * Target.Offset(, 2) = rPart.Offset(, 1) * * * * * * Set Target = Target.Offset(1) * * * * * * Set rPart = rPart.Offset(1) * * * * Loop * * * * Target.Offset(, -1) = "Project Price" * * * * Target = Worksheets("Planner").Range("TermModel.price") End Sub- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
thank you :) but now it doesn't like the next line either
Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).Value it's saying it's not defined? "Per Jessen" wrote: Typo alert, the line should read: Do while .Range("C" & SourceRowCount) < "" Regards, Per On 20 Aug., 04:45, Heather wrote: Thank you, but I'm getting syntax errors on this line Do while .Range("C" SourcerowCount) < "" "Joel" wrote: I really don't like your programming style. it is hard to follow. I rewrote youcode below. It is much easier to understand like the changes I made below. You should easily be able to change to column E with the code below. The problem with your orignal code is something the old column d is being put in column e on the new sheet. to get an addition row on the target sheet you have to increment Targetrow twice in the code below ---------------------------------------------------------------------------Â*------------ sample of how to add an additional row. TargetRow is incremented twice. Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 'put the subtotals in here TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 ---------------------------------------------------------------------------Â*----------------- Sub CreateTab1() Dim Targetws As Worksheet Set Targetws = gettab("tab1") Set Sourcews = Worksheets("Addtl Products") With Targetws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") TargetRow = 4 End With with Sourcews SourceRowCount = 12 Do while .Range("C" SourcerowCount) < "" Targetws.Range("C" & Targetrow) = .Range("C" & Sourcerow).value Targetws.Range("D" & Targetrow) = .Range("B" & Sourcerow).value Targetws.Range("E" & Targetrow) = .Range("D" & Sourcerow).value TargetRow = Targetrow + 1 SourceRowCount = SourceRowCount + 1 loop end with Targetws.Range("B" & Targetrow) = "Project Price" Set rPart = Worksheets("Addtl Products").Range("C12") End Sub "Heather" wrote: Hi, I have created with the very much appreciated help from these discussion boards a workbook that includes a tab that has a high level summary price based on users input and then a hyperlink that once clicked on will break out the prices based on their choices at a more detailed level I'm struggling with 3 things 1) Getting the project price to move over to column E it is currently in Column C 2) Adding a Total Addt'l products price between the new rows and the Project Price 3) Is there a way to incorporate all of this data into a Word letter when it's all done? Here's the code Sub CreateTab1() Dim ws As Worksheet Set ws = gettab("tab1") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("ProjectModel.price") End Sub Sub CreateTab2() Dim ws As Worksheet Set ws = gettab("tab2") Dim rPart As Range Dim Target As Range With ws .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") Set Target = .Range("C4") End With Set rPart = Worksheets("Addtl Products").Range("C12") Do Until rPart = "" Target = rPart.Value Target.Offset(, 1) = rPart.Offset(, -1).Value Target.Offset(, 2) = rPart.Offset(, 1) Set Target = Target.Offset(1) Set rPart = rPart.Offset(1) Loop Target.Offset(, -1) = "Project Price" Target = Worksheets("Planner").Range("TermModel.price") End Sub- Skjul tekst i anførselstegn - - Vis tekst i anførselstegn - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
Looks like Joel introduced the Sourcerow and Targetrow variables to
store the row numbers that you had hard coded. You need to define these variables and assign them values. Phil Hibbs. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
hi Phil, sorry, I'm new to this so I'm not completely sure what you mean by
assigning them values? "Phil Hibbs" wrote: Looks like Joel introduced the Sourcerow and Targetrow variables to store the row numbers that you had hard coded. You need to define these variables and assign them values. Phil Hibbs. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro VBA Help please :)
there were a few typos in my code. There are additional macros in your code
that you didn't post. the following line won't work unless there if a function in your workbook called gettab Set TargetWs = gettab("tab1") Sub CreateTab1() Dim TargetWs As Worksheet Set TargetWs = gettab("tab1") Set Sourcews = Worksheets("Addtl Products") With TargetWs .Range("B3") = "Developers" .Range("C3") = Worksheets("Planner").Range("B5") TargetRow = 4 End With With Sourcews SourceRow = 12 Do While .Range("C" & SourceRow) < "" TargetWs.Range("C" & TargetRow) = .Range("C" & SourceRow).Value TargetWs.Range("D" & TargetRow) = .Range("B" & SourceRow).Value TargetWs.Range("E" & TargetRow) = .Range("D" & SourceRow).Value TargetRow = TargetRow + 1 SourcerowCount = SourcerowCount + 1 Loop End With TargetWs.Range("B" & TargetRow) = "Project Price" Set rPart = Worksheets("Addtl Products").Range("C12") End Sub "Heather" wrote: hi Phil, sorry, I'm new to this so I'm not completely sure what you mean by assigning them values? "Phil Hibbs" wrote: Looks like Joel introduced the Sourcerow and Targetrow variables to store the row numbers that you had hard coded. You need to define these variables and assign them values. Phil Hibbs. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
AutoRun Macro with a delay to give user the choice to cancel the macro | Excel Programming | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro not showing in Tools/Macro/Macros yet show up when I goto VBA editor | Excel Programming | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |