Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a formula & For...Each
Thanks for the previous help! New challenges (for me)...
1) I have columns with grades that are listed on a 0 - 100 scale. I need to divide each one by ten, and keep the new value in the cell. So, if Cell A10 has 90, I need to tell the macro to divide it by 10, and place the new value in Cell A10. I was thinking... (this is based on the correct cell being selected already) Dim tenth as integer tenth = activecell.value / 10 ActiveCell.FormulaR1C1 = tenth '(this can't be right) 2) Now that I have learned (thanks to Luke) how to search based on the name of a cell, I was trying to do the following... AccelTest Results.txt has rows: 1 Assignment Title 2 Assignment Type 3 Points Value 4 Student Score SG GB S2009.xlsm has rows 7 Assignment Title 8 Date 9 Points Value 10 Headers 11 Student Score I need to: 1) Open AccelTest Results.txt (know how to do that) 2) Select first cell with assignment title (know how to do that) 3) Place assignment title as variable "atitle" (know how to do that) 4) Active SG GB S2009.xlsm (know how to do that) 5) Set it to row 7 (know how to do that) Where I am having trouble... (mainly with how to run this over and over) 6) Search every other column, beginning with B, for that assignment title 7) If it finds the assignment title in SG GB S2009.xlsm, skip down to row 11 8) Activate AccelTest Results.txt 9) Skip down to Row 4 (the first with a student's score) 10) Store that number (Student A's score) 11) Activate SG GB S2009.xlsm 12) Compare Student A's score on that assignment in AccelTest Results.txt to Student A's score on that assignment in SG GB S2009.xlsm 13) If it's the equal to or lesser than, move down to Student B's score in AccelTest Results.txt and repeats steps 10 - 13. 14) If it's greater than the old score (the score in SG GB S2009.xlsm), replace that score, then repeat beginning with step 10. 15) It would do this til it runs to the first empty cell (which I know how to code) 16) It would repeat this process with each column in AccelTest Results.txt that has a value in it. I know this was very long and I'm very sorry... I'm not asking for anyone to do this for me... I just need a nudge in the right direction, and I'll do the work... I think a For command would be best, because when I try it as a Do Loop with If statements, I run into having to repeat the steps an exact number of times (and the number of times needed to run the process of comparing and replacing scores varies by class). I don't understand exactly what the For statement does, or how it works, or what is possible with it. Thanks! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Running a formula & For...Each
Here are my answers (see below). don't use activate or Select instead
specify the two workbooks Set sht = activesheet set oldsht = workbooks("SG GB S2009.xlsm").Sheet("Sheet1") Then use sht and oldsht or use with with sht .Range("A1") end with "SeventhFloorProfessor" wrote: Thanks for the previous help! New challenges (for me)... 1) I have columns with grades that are listed on a 0 - 100 scale. I need to divide each one by ten, and keep the new value in the cell. So, if Cell A10 has 90, I need to tell the macro to divide it by 10, and place the new value in Cell A10. I was thinking... (this is based on the correct cell being selected already) Dim tenth as integer tenth = activecell.value / 10 ----------------------------------------- You don't need a formula just put in the value ActiveCell = tenth ----------------------------------------- ActiveCell.FormulaR1C1 = tenth '(this can't be right) 2) Now that I have learned (thanks to Luke) how to search based on the name of a cell, I was trying to do the following... AccelTest Results.txt has rows: 1 Assignment Title 2 Assignment Type 3 Points Value 4 Student Score SG GB S2009.xlsm has rows 7 Assignment Title 8 Date 9 Points Value 10 Headers 11 Student Score I need to: 1) Open AccelTest Results.txt (know how to do that) 2) Select first cell with assignment title (know how to do that) 3) Place assignment title as variable "atitle" (know how to do that) 4) Active SG GB S2009.xlsm (know how to do that) 5) Set it to row 7 (know how to do that) Where I am having trouble... (mainly with how to run this over and over) 6) Search every other column, beginning with B, for that assignment title RowCount = 7 for colCount = 2 to 25 step 2 Title = Cells(RowCount,Colcount) Score = Cells(RowCount).offset(4,0) OldScore = workbooks("SG GB S2009.xlsm").Sheet("Sheet1").Range(RowCount,colCou nt) next ColCount 7) If it finds the assignment title in SG GB S2009.xlsm, skip down to row 11 8) Activate AccelTest Results.txt 9) Skip down to Row 4 (the first with a student's score) 10) Store that number (Student A's score) 11) Activate SG GB S2009.xlsm 12) Compare Student A's score on that assignment in AccelTest Results.txt to Student A's score on that assignment in SG GB S2009.xlsm 13) If it's the equal to or lesser than, move down to Student B's score in AccelTest Results.txt and repeats steps 10 - 13. 14) If it's greater than the old score (the score in SG GB S2009.xlsm), replace that score, then repeat beginning with step 10. 15) It would do this til it runs to the first empty cell (which I know how to code) 16) It would repeat this process with each column in AccelTest Results.txt that has a value in it. I know this was very long and I'm very sorry... I'm not asking for anyone to do this for me... I just need a nudge in the right direction, and I'll do the work... I think a For command would be best, because when I try it as a Do Loop with If statements, I run into having to repeat the steps an exact number of times (and the number of times needed to run the process of comparing and replacing scores varies by class). I don't understand exactly what the For statement does, or how it works, or what is possible with it. Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Running formula | Excel Discussion (Misc queries) | |||
Running Total by formula | Excel Discussion (Misc queries) | |||
I need a formula to keep a running percentage....HELP!! | Excel Discussion (Misc queries) | |||
Formula for running total | Excel Worksheet Functions | |||
Formula for Running total | Excel Programming |