frade
05-11-2005, 03:16 AM
Hi,
I would like to know the value of the last row in the column "H"
Here is my code
Private Sub calculations()
Dim sDays As String
Dim sDilutions As String
Dim sResults As String
Dim sCountSum As String
Dim sID As String
Dim STANDARD_DEVIATION_BY_DAY As String
Dim STANDARD_DEVIATION_BETWEEN_DAY As String
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim iLastRow3 As Long
Dim i As Long
Dim valB As Long
Dim val2B As Long
Dim sFormula As String
Dim NumDays As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iLastRow2 = Cells(Rows.Count, "H").End(xlUp).Row
iLastRow3 = Worksheets("Sheet2").Cells("H" & iLastRow2).Value
valB = Worksheets("Sheet1").Range("B1").Value
NumDays = Worksheets("Sheet1").Range("B3").Value
val2B = valB * 10
sID = "A2:B" & iLastRow
sDays = "B2:B" & iLastRow
sDilutions = "C2:C" & iLastRow
sResults = "E2:E" & iLastRow
sCountSum = "P2:P" & iLastRow
STANDARD_DEVIATION_BY_DAY = "I2:I" & iLastRow
STANDARD_DEVIATION_BETWEEN_DAY = "K2:K" & iLastRow
For i = 2 To iLastRow
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")," & sResults & "))"
Cells(i, "F").Value = Evaluate(sFormula)
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")*(" & _
sDilutions & "=$C" & i & ")," & sResults & "))"
Cells(i, "G").Value = Evaluate(sFormula)
sFormula = "((E" & i & "-G" & i & ")^2)"
Cells(i, "P").Value = Evaluate(sFormula)
sFormula = "SUM(" & sCountSum & ")"
Cells(i, "H").Value = Evaluate(sFormula)
sFormula = "AVERAGE(" & sResults & ")"
Cells(i, "M").Value = Evaluate(sFormula)
sFormula = "(H" & i & "/" & valB & ")"
Cells(i, "I").Value = Evaluate(sFormula)
sFormula = "(H" & i & "/" & val2B & ")"
Cells(i, "K").Value = Evaluate(sFormula)
sFormula = "SQRT(K" & i & ")"
Cells(i, "L").Value = Evaluate(sFormula)
sFormula = "SQRT(IF((" & sDays & "=$B" & i & ")," & sCountSum & "))"
Cells(i, "J").Value = Evaluate(sFormula)
sFormula = "((L" & i & "/M" & i & "))"
Cells(i, "O").Value = Evaluate(sFormula)
sFormula = "((J" & i & "/F" & i & "))"
Cells(i, "N").Value = Evaluate(sFormula)
sFormula = "( & iLastRow3 & )"
Cells(i, "Q").Value = Evaluate(sFormula)
Next i
End Sub
When I run my code, an error appears on this line
iLastRow3 = Worksheets("Sheet2").Cells("H" & iLastRow2).Value
Thanks,
Fran?ois
I would like to know the value of the last row in the column "H"
Here is my code
Private Sub calculations()
Dim sDays As String
Dim sDilutions As String
Dim sResults As String
Dim sCountSum As String
Dim sID As String
Dim STANDARD_DEVIATION_BY_DAY As String
Dim STANDARD_DEVIATION_BETWEEN_DAY As String
Dim iLastRow As Long
Dim iLastRow2 As Long
Dim iLastRow3 As Long
Dim i As Long
Dim valB As Long
Dim val2B As Long
Dim sFormula As String
Dim NumDays As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iLastRow2 = Cells(Rows.Count, "H").End(xlUp).Row
iLastRow3 = Worksheets("Sheet2").Cells("H" & iLastRow2).Value
valB = Worksheets("Sheet1").Range("B1").Value
NumDays = Worksheets("Sheet1").Range("B3").Value
val2B = valB * 10
sID = "A2:B" & iLastRow
sDays = "B2:B" & iLastRow
sDilutions = "C2:C" & iLastRow
sResults = "E2:E" & iLastRow
sCountSum = "P2:P" & iLastRow
STANDARD_DEVIATION_BY_DAY = "I2:I" & iLastRow
STANDARD_DEVIATION_BETWEEN_DAY = "K2:K" & iLastRow
For i = 2 To iLastRow
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")," & sResults & "))"
Cells(i, "F").Value = Evaluate(sFormula)
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")*(" & _
sDilutions & "=$C" & i & ")," & sResults & "))"
Cells(i, "G").Value = Evaluate(sFormula)
sFormula = "((E" & i & "-G" & i & ")^2)"
Cells(i, "P").Value = Evaluate(sFormula)
sFormula = "SUM(" & sCountSum & ")"
Cells(i, "H").Value = Evaluate(sFormula)
sFormula = "AVERAGE(" & sResults & ")"
Cells(i, "M").Value = Evaluate(sFormula)
sFormula = "(H" & i & "/" & valB & ")"
Cells(i, "I").Value = Evaluate(sFormula)
sFormula = "(H" & i & "/" & val2B & ")"
Cells(i, "K").Value = Evaluate(sFormula)
sFormula = "SQRT(K" & i & ")"
Cells(i, "L").Value = Evaluate(sFormula)
sFormula = "SQRT(IF((" & sDays & "=$B" & i & ")," & sCountSum & "))"
Cells(i, "J").Value = Evaluate(sFormula)
sFormula = "((L" & i & "/M" & i & "))"
Cells(i, "O").Value = Evaluate(sFormula)
sFormula = "((J" & i & "/F" & i & "))"
Cells(i, "N").Value = Evaluate(sFormula)
sFormula = "( & iLastRow3 & )"
Cells(i, "Q").Value = Evaluate(sFormula)
Next i
End Sub
When I run my code, an error appears on this line
iLastRow3 = Worksheets("Sheet2").Cells("H" & iLastRow2).Value
Thanks,
Fran?ois