VBA/ debug help

[emmcee]

hey guys I ran this code and I kept getting a message of
Runtime Error '13':
Type Mismatch

What does that mean, this is the code I ran and when I click on debug the red font is highlighted. Is there something wrong with it?

Sub AverageWindSpeed()
Dim i, LastRow, eTime, wSpeed

'set last used row for including additonal rows, if added
LastRow = Range("E" & Rows.count).End(xlUp).row

'begin in row2 as etime refers to prior row. There is no row prior than row 1
For i = 2 To LastRow

'calculate the elapsed time. Subtracting the cells returns a decimal value of a day.
'Multiply by 24 to return hours.
eTime = eTime + (Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24

'calculate the accumulated total wind speed by adding each increment to the accumulated total
wSpeed = wSpeed + ((Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24) * (Cells(i - 1, "F").Value)

'loop
Next

'set average wind speed in cell B13
Range("B13").Value = Round(wSpeed / eTime, 2)

End Sub


Thanks helps

 

[AlphaFrog]

If you have any text or error values in column "E", you will get a type missmatch error because you cannot use arithmetic operators on those values.

Maybe try something like this...

    For i = 2 To Lastrow
        'calculate the elapsed time. Subtracting the cells returns a decimal value of a day.
        'Multiply by 24 to return hours.
        [COLOR="Red"]If IsNumeric(Cells(i, "E").Value) And IsNumeric(Cells(i - 1, "E").Value) Then[/COLOR]
            eTime = eTime + (Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24

            'calculate the accumulated total wind speed by adding each increment to the accumulated total
            [COLOR="Red"]If IsNumeric(Cells(i - 1, "F").Value) Then[/COLOR]
                wSpeed = wSpeed + ((Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24) * (Cells(i - 1, "F").Value)
            [COLOR="Red"]End If: End If[/COLOR]
        'loop
    Next

 

[emmcee]

Hey AlphaFrog I tried your one and this time it said the same thing but on the font thats highlighted red. I used that line again on my previous code because B17 is where the answer is meant to go. Is this wrong?
Thank you for replying


Sub AverageWindSpeed()
Dim i, LastRow, eTime, wSpeed

'set last used row for including additonal rows, if added
LastRow = Range("E" & Rows.count).End(xlUp).row

'begin in row2 as etime refers to prior row. There is no row prior than row 1
For i = 2 To LastRow
'calculate the elapsed time. Subtracting the cells returns a decimal value of a day.
'Multiply by 24 to return hours.
If IsNumeric(Cells(i, "E").Value) And IsNumeric(Cells(i - 1, "E").Value) Then
eTime = eTime + (Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24

'calculate the accumulated total wind speed by adding each increment to the accumulated total
If IsNumeric(Cells(i - 1, "F").Value) Then
wSpeed = wSpeed + ((Cells(i, "E").Value - Cells(i - 1, "E").Value) * 24) * (Cells(i - 1, "F").Value)
End If: End If
'loop
Next
'set average wind speed in cell B13
Range("B13").Value = Round(wSpeed / eTime, 2)
End Sub

 

[AlphaFrog]

Do you really want to do this in VBA? You could do it with a formula.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Time</td><td style="font-weight: bold;text-align: center;;">Speed</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">9:25 PM</td><td style="text-align: center;;">2</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">9:26 PM</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">9:27 PM</td><td style="text-align: center;;">4</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">9:28 PM</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">9:29 PM</td><td style="text-align: center;;">6</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">9:30 PM</td><td style="text-align: center;;">7</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">9:31 PM</td><td style="text-align: center;;">8</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">9:32 PM</td><td style="text-align: center;;">9</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">9:33 PM</td><td style="text-align: center;;">10</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="font-weight: bold;text-align: center;;">Avg Wind Speed</td><td style="text-align: center;background-color: #FFFF99;;">5.50</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F12</th><td style="text-align:left">=SUMPRODUCT(<font color="Blue">(<font color="Red">E3:E10-E2:E9</font>)*24*F2:F9</font>)/SUMPRODUCT(<font color="Blue">(<font color="Red">E3:E10-E2:E9</font>)*24</font>)</td></tr></tbody></table></td></tr></table><br />

 

[emmcee]

Yeah I was hoping to use the code you gave me but I'm not sure why a Debug Error kept popping up.

For my homework I can't use excel only I have to write a code to solve this in the VBA module and for it to work.