Running a formula & For...Each

[SeventhFloorProfessor]

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!

 

[Joel]

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

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,colCount)
next ColCount