Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direction
I have never written a macro in Excel, and I need big-time help.
I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direction
First, how about a slight change in your worksheets.
Create one sheet that contains the input cells and (soon to be) output cells. Then create another sheet that contains all the calculations that you need. Then you type in all the input cells and the macro takes each line of data, populates the calc sheet, calculates, and then extracts the resulting cells that you want and places them onto the same row as the input values on that input sheet. If that sounds reasonable, here's a macro that may get you started... Seems like a reasonable approach to me: You'll have to change this to use the right cells on the calculation sheet: Option Explicit Sub testme() Dim InputWks As Worksheet Dim CalcWks As Worksheet Dim myRng As Range Dim myCell As Range Set InputWks = Worksheets("sheet1") Set CalcWks = Worksheets("sheet2") With InputWks 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With CalcWks For Each myCell In myRng.Cells 'populate the CalcWks with values from the input sheet .Range("a1").Value = myCell.Value .Range("x99").value = mycell.offset(0,1).value .range("iv323").value = mycell.offset(0,2).value 'do the calculation Application.Calculate 'take some values back from the calcwks to the input sheet myCell.Offset(0, 3).Value = .Range("b1").Value myCell.Offset(0, 4).Value = .Range("c1").Value myCell.Offset(0, 5).Value = .Range("d1").Value Next myCell End With End Sub You'll have to change all the addresses that get populated and add more lines as you need them. And same thing with the "after calc" portion. You'll want to put them in the cells you want. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) GoBrowns! wrote: I have never written a macro in Excel, and I need big-time help. I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direc
Hi Dave;
I don't have any leeway in how the worksheet is set up, but I may have miscommunicated something. I don't need the macro to average the figures that I need placed in a new worksheet - those are simply the names of the figures. What part of this code would I need to just move my figures from one sheet to another? I also need to be able to delete data from Worksheet 1 once I move it, then input new data and send it to the cells directly below the cells I already sent over. Thanks for the help - if you need more info from me, please let me know!! "Dave Peterson" wrote: First, how about a slight change in your worksheets. Create one sheet that contains the input cells and (soon to be) output cells. Then create another sheet that contains all the calculations that you need. Then you type in all the input cells and the macro takes each line of data, populates the calc sheet, calculates, and then extracts the resulting cells that you want and places them onto the same row as the input values on that input sheet. If that sounds reasonable, here's a macro that may get you started... Seems like a reasonable approach to me: You'll have to change this to use the right cells on the calculation sheet: Option Explicit Sub testme() Dim InputWks As Worksheet Dim CalcWks As Worksheet Dim myRng As Range Dim myCell As Range Set InputWks = Worksheets("sheet1") Set CalcWks = Worksheets("sheet2") With InputWks 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With CalcWks For Each myCell In myRng.Cells 'populate the CalcWks with values from the input sheet .Range("a1").Value = myCell.Value .Range("x99").value = mycell.offset(0,1).value .range("iv323").value = mycell.offset(0,2).value 'do the calculation Application.Calculate 'take some values back from the calcwks to the input sheet myCell.Offset(0, 3).Value = .Range("b1").Value myCell.Offset(0, 4).Value = .Range("c1").Value myCell.Offset(0, 5).Value = .Range("d1").Value Next myCell End With End Sub You'll have to change all the addresses that get populated and add more lines as you need them. And same thing with the "after calc" portion. You'll want to put them in the cells you want. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) GoBrowns! wrote: I have never written a macro in Excel, and I need big-time help. I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direc
Option Explicit
Sub testme() dim DataWks as worksheet dim TabWks as worksheet dim NextRow as long set datawks = worksheets("sheetname that user types in") set tabwks = worksheets("sheetname that looks like a table") with tabwks 'find the next available row based on the stuff in column A nextrow = .cells(.rows.count,"A").end(xlup).row + 1 end with with datawks 'copy the value in A1 to the next row in column A and clear A1 tabwks.cells(nextrow,"A").value = .range("a1").value .range("A1").clearcontents 'copy the value in x99 to the next row in column B and clear X99 tabwks.cells(nextrow,"B").value = .range("x99").value .range("x99").clearcontents 'and so forth end with End Sub (Untested, uncompiled. watch for typos.) GoBrowns! wrote: Hi Dave; I don't have any leeway in how the worksheet is set up, but I may have miscommunicated something. I don't need the macro to average the figures that I need placed in a new worksheet - those are simply the names of the figures. What part of this code would I need to just move my figures from one sheet to another? I also need to be able to delete data from Worksheet 1 once I move it, then input new data and send it to the cells directly below the cells I already sent over. Thanks for the help - if you need more info from me, please let me know!! "Dave Peterson" wrote: First, how about a slight change in your worksheets. Create one sheet that contains the input cells and (soon to be) output cells. Then create another sheet that contains all the calculations that you need. Then you type in all the input cells and the macro takes each line of data, populates the calc sheet, calculates, and then extracts the resulting cells that you want and places them onto the same row as the input values on that input sheet. If that sounds reasonable, here's a macro that may get you started... Seems like a reasonable approach to me: You'll have to change this to use the right cells on the calculation sheet: Option Explicit Sub testme() Dim InputWks As Worksheet Dim CalcWks As Worksheet Dim myRng As Range Dim myCell As Range Set InputWks = Worksheets("sheet1") Set CalcWks = Worksheets("sheet2") With InputWks 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With CalcWks For Each myCell In myRng.Cells 'populate the CalcWks with values from the input sheet .Range("a1").Value = myCell.Value .Range("x99").value = mycell.offset(0,1).value .range("iv323").value = mycell.offset(0,2).value 'do the calculation Application.Calculate 'take some values back from the calcwks to the input sheet myCell.Offset(0, 3).Value = .Range("b1").Value myCell.Offset(0, 4).Value = .Range("c1").Value myCell.Offset(0, 5).Value = .Range("d1").Value Next myCell End With End Sub You'll have to change all the addresses that get populated and add more lines as you need them. And same thing with the "after calc" portion. You'll want to put them in the cells you want. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) GoBrowns! wrote: I have never written a macro in Excel, and I need big-time help. I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direc
Dave;
Here is what I put in: Option Explicit Sub Zone1() Dim DataWks As Worksheet Dim TabWks As Worksheet Dim NextRow As Long Set DataWks = Worksheets("Zone 1-Color Crews") Set TabWks = Worksheets("Tables") With TabWks 'find the next available row based on the stuff in column A NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With With DataWks 'copy the value in C7 to the next row in column A and clear C7 TabWks.Cells(NextRow, "A").Value = .Range("C7").Value .Range("C7").ClearContents 'copy the value in D7 to the next row in column A and clear D7 TabWks.Cells(NextRow, "B").Value = .Range("D7").Value .Range("D7").ClearContents 'copy the value in F7 to the next row in column A and clear F7 TabWks.Cells(NextRow, "C").Value = .Range("F7").Value .Range("F7").ClearContents 'copy the value in G7 to the next row in column A and clear G7 TabWks.Cells(NextRow, "D").Value = .Range("G7").Value .Range("G7").ClearContents 'copy the value in H7 to the next row in column B and clear H7 TabWks.Cells(NextRow, "E").Value = .Range("H7").Value .Range("H7").ClearContents 'copy the value in I7 to the next row in column B and clear I7 TabWks.Cells(NextRow, "F").Value = .Range("I7").Value .Range("I7").ClearContents End With End Sub I keep getting a run-time erro: "Application-defined or object defined error." I tried to look this up online, and all I found was that this occurs when you are trying to copy/paste from one worksheet to another in the same workbook. Is there something I can do about that? Also, just to be su the values I put in to be copied are from my worksheet the users types into... the columns represent the columns in the worksheet that has the table I am pasting to. Right? Thanks for the help!!!!! "Dave Peterson" wrote: Option Explicit Sub testme() dim DataWks as worksheet dim TabWks as worksheet dim NextRow as long set datawks = worksheets("sheetname that user types in") set tabwks = worksheets("sheetname that looks like a table") with tabwks 'find the next available row based on the stuff in column A nextrow = .cells(.rows.count,"A").end(xlup).row + 1 end with with datawks 'copy the value in A1 to the next row in column A and clear A1 tabwks.cells(nextrow,"A").value = .range("a1").value .range("A1").clearcontents 'copy the value in x99 to the next row in column B and clear X99 tabwks.cells(nextrow,"B").value = .range("x99").value .range("x99").clearcontents 'and so forth end with End Sub (Untested, uncompiled. watch for typos.) GoBrowns! wrote: Hi Dave; I don't have any leeway in how the worksheet is set up, but I may have miscommunicated something. I don't need the macro to average the figures that I need placed in a new worksheet - those are simply the names of the figures. What part of this code would I need to just move my figures from one sheet to another? I also need to be able to delete data from Worksheet 1 once I move it, then input new data and send it to the cells directly below the cells I already sent over. Thanks for the help - if you need more info from me, please let me know!! "Dave Peterson" wrote: First, how about a slight change in your worksheets. Create one sheet that contains the input cells and (soon to be) output cells. Then create another sheet that contains all the calculations that you need. Then you type in all the input cells and the macro takes each line of data, populates the calc sheet, calculates, and then extracts the resulting cells that you want and places them onto the same row as the input values on that input sheet. If that sounds reasonable, here's a macro that may get you started... Seems like a reasonable approach to me: You'll have to change this to use the right cells on the calculation sheet: Option Explicit Sub testme() Dim InputWks As Worksheet Dim CalcWks As Worksheet Dim myRng As Range Dim myCell As Range Set InputWks = Worksheets("sheet1") Set CalcWks = Worksheets("sheet2") With InputWks 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With CalcWks For Each myCell In myRng.Cells 'populate the CalcWks with values from the input sheet .Range("a1").Value = myCell.Value .Range("x99").value = mycell.offset(0,1).value .range("iv323").value = mycell.offset(0,2).value 'do the calculation Application.Calculate 'take some values back from the calcwks to the input sheet myCell.Offset(0, 3).Value = .Range("b1").Value myCell.Offset(0, 4).Value = .Range("c1").Value myCell.Offset(0, 5).Value = .Range("d1").Value Next myCell End With End Sub You'll have to change all the addresses that get populated and add more lines as you need them. And same thing with the "after calc" portion. You'll want to put them in the cells you want. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) GoBrowns! wrote: I have never written a macro in Excel, and I need big-time help. I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can someone help me get started or point me in the right direc
The code ran fine for me.
I don't have any good guesses. What line causes the error? Is the worksheet getting the data protected? Do you have merged cells? Just random guesses GoBrowns! wrote: Dave; Here is what I put in: Option Explicit Sub Zone1() Dim DataWks As Worksheet Dim TabWks As Worksheet Dim NextRow As Long Set DataWks = Worksheets("Zone 1-Color Crews") Set TabWks = Worksheets("Tables") With TabWks 'find the next available row based on the stuff in column A NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 End With With DataWks 'copy the value in C7 to the next row in column A and clear C7 TabWks.Cells(NextRow, "A").Value = .Range("C7").Value .Range("C7").ClearContents 'copy the value in D7 to the next row in column A and clear D7 TabWks.Cells(NextRow, "B").Value = .Range("D7").Value .Range("D7").ClearContents 'copy the value in F7 to the next row in column A and clear F7 TabWks.Cells(NextRow, "C").Value = .Range("F7").Value .Range("F7").ClearContents 'copy the value in G7 to the next row in column A and clear G7 TabWks.Cells(NextRow, "D").Value = .Range("G7").Value .Range("G7").ClearContents 'copy the value in H7 to the next row in column B and clear H7 TabWks.Cells(NextRow, "E").Value = .Range("H7").Value .Range("H7").ClearContents 'copy the value in I7 to the next row in column B and clear I7 TabWks.Cells(NextRow, "F").Value = .Range("I7").Value .Range("I7").ClearContents End With End Sub I keep getting a run-time erro: "Application-defined or object defined error." I tried to look this up online, and all I found was that this occurs when you are trying to copy/paste from one worksheet to another in the same workbook. Is there something I can do about that? Also, just to be su the values I put in to be copied are from my worksheet the users types into... the columns represent the columns in the worksheet that has the table I am pasting to. Right? Thanks for the help!!!!! "Dave Peterson" wrote: Option Explicit Sub testme() dim DataWks as worksheet dim TabWks as worksheet dim NextRow as long set datawks = worksheets("sheetname that user types in") set tabwks = worksheets("sheetname that looks like a table") with tabwks 'find the next available row based on the stuff in column A nextrow = .cells(.rows.count,"A").end(xlup).row + 1 end with with datawks 'copy the value in A1 to the next row in column A and clear A1 tabwks.cells(nextrow,"A").value = .range("a1").value .range("A1").clearcontents 'copy the value in x99 to the next row in column B and clear X99 tabwks.cells(nextrow,"B").value = .range("x99").value .range("x99").clearcontents 'and so forth end with End Sub (Untested, uncompiled. watch for typos.) GoBrowns! wrote: Hi Dave; I don't have any leeway in how the worksheet is set up, but I may have miscommunicated something. I don't need the macro to average the figures that I need placed in a new worksheet - those are simply the names of the figures. What part of this code would I need to just move my figures from one sheet to another? I also need to be able to delete data from Worksheet 1 once I move it, then input new data and send it to the cells directly below the cells I already sent over. Thanks for the help - if you need more info from me, please let me know!! "Dave Peterson" wrote: First, how about a slight change in your worksheets. Create one sheet that contains the input cells and (soon to be) output cells. Then create another sheet that contains all the calculations that you need. Then you type in all the input cells and the macro takes each line of data, populates the calc sheet, calculates, and then extracts the resulting cells that you want and places them onto the same row as the input values on that input sheet. If that sounds reasonable, here's a macro that may get you started... Seems like a reasonable approach to me: You'll have to change this to use the right cells on the calculation sheet: Option Explicit Sub testme() Dim InputWks As Worksheet Dim CalcWks As Worksheet Dim myRng As Range Dim myCell As Range Set InputWks = Worksheets("sheet1") Set CalcWks = Worksheets("sheet2") With InputWks 'headers in row 1 Set myRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp)) End With With CalcWks For Each myCell In myRng.Cells 'populate the CalcWks with values from the input sheet .Range("a1").Value = myCell.Value .Range("x99").value = mycell.offset(0,1).value .range("iv323").value = mycell.offset(0,2).value 'do the calculation Application.Calculate 'take some values back from the calcwks to the input sheet myCell.Offset(0, 3).Value = .Range("b1").Value myCell.Offset(0, 4).Value = .Range("c1").Value myCell.Offset(0, 5).Value = .Range("d1").Value Next myCell End With End Sub You'll have to change all the addresses that get populated and add more lines as you need them. And same thing with the "after calc" portion. You'll want to put them in the cells you want. If you're new to macros: Debra Dalgleish has some notes how to implement macros he http://www.contextures.com/xlvba01.html David McRitchie has an intro to macros: http://www.mvps.org/dmcritchie/excel/getstarted.htm Ron de Bruin's intro to macros: http://www.rondebruin.nl/code.htm (General, Regular and Standard modules all describe the same thing.) GoBrowns! wrote: I have never written a macro in Excel, and I need big-time help. I need a macro that will take the following fields from one worksheet: Date Average SORT Score Average SET IN ORDER Score Average SHINE Score Average STANDARDIZE Score Average SUSTAIN Score Average TOTAL Score Then, I need the macro to place these fields in a new worksheet in a horizontal table. Once that is complete, I would like for a macro to clear the initial worksheet, then allow me to re-run the first macro so that I can have the same fields placed onto the second worksheet under the data that has already collected there. In short, I want my second worksheet to look like this: DATE SORT SET IN ORDER SHINE STANDARDIZE SUSTAIN TOTAL Jan 09 Feb 09 Mar 09 And so on, with all data points filled in. Is this possible? And if so, how the heck do I go about doing it? I am willing to provide any and all needed additional information. THANKS SO MUCH FOR YOUR HELP!!!! -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Point me in the right direction | Excel Programming | |||
A point in the right direction... | Excel Programming | |||
Data point on line is not over the point/tick in X axis... | Charts and Charting in Excel | |||
Can anyone point me in the right direction please? | Excel Programming | |||
I need more general XY point to point plotting than XY scatter in | Charts and Charting in Excel |