Sleeper: How to obtain the value of the last row?

[frade]

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

[Bob Phillips]

Replace


iLastRow3 = Worksheets("Sheet2").Cells("H" & iLastRow2).Value

with


iLastRow3 = Worksheets("Sheet2").Range("H" & iLastRow2).Value

[frade]

I have replaced cells by range..
Sorry but It's not enough...run time error'13' type mismatch

Where is my error?

Thanks,

Fran?ois

[Bob Phillips]

I have replaced cells by range..
Sorry but It's not enough...run time error'13' type mismatch

Where is my error?


iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
iLastRow2 = Cells(Rows.Count, "H").End(xlUp).Row
iLastRow3 = Worksheets("Sheet2").Range("H" & iLastRow2).Value

That is because the iLastRow2 resolves to 1 because column H only has a header, so that when you try to get iLastRow3 using iLastRow2 you get cell H1, which is a string and iLastRow3 is a Long - QED Type Mismatch.

Your logic looks flawed. What are you trying to get in iLastRow2?

[Paleo]

frade,

if all what you need is the value out of the last row in column "H", why dont you use this?



vLstRow = Range("H65536").End(xlUp)

[frade]

Ok, In fact iLastRow2 is not usefull.
For each line in my column "Q" I would like to display the last value of the column "H"
in my example 89,13

What can I change in this line or elsewhere?

sFormula = "( & iLastRow3 & )"
Cells(i, "Q").Value = Evaluate(sFormula)

Fran?ois

[Paleo]

Hi Fran?ois,

to display the last value of the column "H", simply modify the code to this:



Cells(i, "Q") = Range("H65536").End(xlUp)


Of course I am assuming both columns are from the same sheet. Are they?

[frade]

I have changed but I have always differents values in the column Q and not only
the last value of column
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 iLastRowOK 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
iLastRowOK = Worksheets("Sheet2").Range("H" & iLastRow).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)
Cells(i, "Q").Value = Range("H65536").End(xlUp)
sFormula = "(Q2/" & val2B & ")"
Cells(i, "R").Value = Evaluate(sFormula)
Next i
End Sub

H:help Thanks for your help!

[Paleo]

Fran?ois,

start by simplifying your code:



iLastRow = Range("A65536").End(xlUp).Row
iLastRow2 = Range("H65536").End(xlUp).Row
iLastRowOK = Worksheets("Sheet2").Range("H" & iLastRow)
valB = Worksheets("Sheet1").Range("B1")
NumDays = Worksheets("Sheet1").Range("B3")


Pay attention at this line:


iLastRowOK = Worksheets("Sheet2").Range("H" & iLastRow)


Are you sure its correct? You are looking for the value in column "H" corresponding to the last row from column "A". What if the columns length is different?

[frade]

Ok, It's seems better like this.

Thanks for your advice

Fran?ois

[johnske]

Hi Francois,

Had a look at your attachment, I'm not sure what your results should be, but this mod gets rid of the error when run on my machine. See if it gives you the results you're after...


Private Sub calculations()
Dim sDays$, sDilutions$, sResults$
Dim sCountSum$, sID$, sFormula$
Dim STANDARD_DEVIATION_BY_DAY$
Dim STANDARD_DEVIATION_BETWEEN_DAY$
Dim iLastRow&, iLastRow2&, iLastRow3&
Dim i&, valB&, val2B&, NumDays&
iLastRow = [A65536].End(xlUp).Row
iLastRow2 = [H65536].End(xlUp).Row
iLastRow3 = Range("Sheet2!H" & iLastRow2)
valB = [Sheet1!B1]
NumDays = [Sheet1!B3]
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") = Evaluate(sFormula)
sFormula = "AVERAGE(IF((" & sDays & "=$B" & i & ")*(" & _
sDilutions & "=$C" & i & ")," & sResults & "))"
Cells(i, "G") = Evaluate(sFormula)
sFormula = "((E" & i & "-G" & i & ")^2)"
Cells(i, "P") = Evaluate(sFormula)
sFormula = "SUM(" & sCountSum & ")"
Cells(i, "H") = Evaluate(sFormula)
sFormula = "AVERAGE(" & sResults & ")"
Cells(i, "M") = Evaluate(sFormula)
sFormula = "(H" & i & "/" & valB & ")"
Cells(i, "I") = Evaluate(sFormula)
sFormula = "(H" & i & "/" & val2B & ")"
Cells(i, "K") = Evaluate(sFormula)
sFormula = "SQRT(K" & i & ")"
Cells(i, "L") = Evaluate(sFormula)
sFormula = "SQRT(IF((" & sDays & "=$B" & i & ")," & sCountSum & "))"
Cells(i, "J") = Evaluate(sFormula)
sFormula = "((L" & i & "/M" & i & "))"
Cells(i, "O") = Evaluate(sFormula)
sFormula = "((J" & i & "/F" & i & "))"
Cells(i, "N") = Evaluate(sFormula)
sFormula = "( & iLastRow3 & )"
Cells(i, "Q") = Evaluate(sFormula)
Next i
End Sub

PS changed a few things just to make it a bit easier for me to read.

HTH,
John