Do a calculation whilst in a loop Excel 2003

질문

• What I am trying to do is perform a calculation whilst in a loop. I want it to subtract one cell from the other then if the product is not = 0.5m or  -0.5m then delete the row. I am stumped at how to do the calculation in a loop, and even more confused how to define both a down going limit and an up going limit (when the dataset is going down if the difference is between 0 and ~0.4m then it is most likely a duplicate reading as it triggers off pressure, on the way up the duplicate readings will be seen between 0 and ~ -0.4m)

I guess I could have two calculations, one for going down and one for going up... but for the moment I am just concentrating on one...

Below is what I have in my head! I know it's not right, but I think if somebody reads it they will see what I am trying to do and advise accordingly.

Many thanks in advance for any help,

Cheers,

Mark

``` For i = 3 To nb_last_row
With Worksheets("Table")
' Delete all rows where difference in depth is not 0.5m
If (.Cells(i, 2).Value - .Cells(i -1, 2).Value) < 0.5 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If

If i = nb_last_row Then Exit For
End With
Next i
End If```

• 편집됨 2012년 6월 10일 일요일 오전 5:32
2012년 6월 10일 일요일 오전 5:32

답변

• That means that at least one of those two cells is not numeric.

You could either fix it manually (just scan through the list and fix the errors)

or you could include some checks in your code:

if isnumeric(.cells(1,2).value) _
and isnumeric(.cells(i-1,2).value) then
'do the calculation and possible delete
else
'do something else -- warning or what
end if

untested, uncompiled.  Watch for typos.

marky9074 wrote:

Hi Ed,

Thanks for the quick reply, it's much appreciated :)
Unfortunately I still get the same on this line:

` If (.Cells(i, 2).Value - .Cells(i - 1, 2).Value) < 0.5 Then`
`Run-time error '13':Type mismatch`

That's what lead me to think in the first place that my calculation in the loop was not quite correct :/

Perhaps I should have made that clearer earlier..

--

Dave Peterson

2012년 6월 10일 일요일 오전 10:36

모든 응답

• Hi Mark,

You are almost there. The trick with deleting rows is to go up from the bottom. So try it this way...

`With Worksheets("Table")  For i = nb_last_row To 3 Step -1    If (.Cells(i, 2).Value - .Cells(i - 1, 2).Value) < 0.5 Then       .Cells(i, 2).EntireRow.Delete shift:=xlShiftUp    End If  Next iEnd With`

Ed Ferrero
www.edferrero.com

2012년 6월 10일 일요일 오전 7:17
• Hi Ed,

Thanks for the quick reply, it's much appreciated :)

Unfortunately I still get the same on this line:

` If (.Cells(i, 2).Value - .Cells(i - 1, 2).Value) < 0.5 Then`

`Run-time error '13':Type mismatch`

That's what lead me to think in the first place that my calculation in the loop was not quite correct :/

Perhaps I should have made that clearer earlier..

2012년 6월 10일 일요일 오전 9:20
• That means that at least one of those two cells is not numeric.

You could either fix it manually (just scan through the list and fix the errors)

or you could include some checks in your code:

if isnumeric(.cells(1,2).value) _
and isnumeric(.cells(i-1,2).value) then
'do the calculation and possible delete
else
'do something else -- warning or what
end if

untested, uncompiled.  Watch for typos.

marky9074 wrote:

Hi Ed,

Thanks for the quick reply, it's much appreciated :)
Unfortunately I still get the same on this line:

` If (.Cells(i, 2).Value - .Cells(i - 1, 2).Value) < 0.5 Then`
`Run-time error '13':Type mismatch`

That's what lead me to think in the first place that my calculation in the loop was not quite correct :/

Perhaps I should have made that clearer earlier..

--

Dave Peterson

2012년 6월 10일 일요일 오전 10:36
• Nope, I don't think so. I am already doing calculations in a loop for other things using these cells, so it can't be that it's not numeric..

```      ' Calculate the latitude value in DD.ddddd needed to convert dbar in meters for water depth
latitude = Worksheets("Main menu").Cells(22, 2).Value * 24
If (Worksheets("Main menu").Cells(22, 3).Value = "S") Then latitude = latitude * (-1)

For i = 3 To nb_last_row
With Worksheets("Table")
.Range("C" & i & "").Value = Convert_water_depth_pressure_to_meter(CDbl(.Range("C" & i & "").Value), latitude)
' Delete all rows that have dept < 0.5 m
If .Cells(i, 3).Value < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows with velocity < 1450 (normal values are between 1450 and 1550)
If .Cells(i, 2).Value < 1450 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If

If i = nb_last_row Then Exit For
End With
Next i
End If```

All I am doing is adding my bit at the end (excuse the correction as I had the wrong cells in my original post)..

``` ' Calculate the latitude value in DD.ddddd needed to convert dbar in meters for water depth
latitude = Worksheets("Main menu").Cells(22, 2).Value * 24
If (Worksheets("Main menu").Cells(22, 3).Value = "S") Then latitude = latitude * (-1)

For i = 3 To nb_last_row
With Worksheets("Table")
.Range("C" & i & "").Value = Convert_water_depth_pressure_to_meter(CDbl(.Range("C" & i & "").Value), latitude)
' Delete all rows that have dept < 0.5 m
If .Cells(i, 3).Value < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows with velocity < 1450 (normal values are between 1450 and 1550)
If .Cells(i, 2).Value < 1450 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows where difference in depth is not 0.5m
If (.Cells(i, 3).Value - .Cells(i - 1, 3).Value) < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
If i = nb_last_row Then Exit For
End With
Next i
End If```

• 편집됨 2012년 6월 11일 월요일 오전 3:34
2012년 6월 11일 월요일 오전 3:29
• I'd add the check and pop up a msgbox if there was non-numeric data--maybe
include the row numbers to make it easy to check.

marky9074 wrote:

Nope, I don't think so. I am already doing calculations in a loop for other things using these cells, so it can't be that it's not numeric..

```            ' Calculate the latitude value in DD.ddddd needed to convert dbar in meters for water depth
latitude = Worksheets("Main menu").Cells(22, 2).Value * 24
If (Worksheets("Main menu").Cells(22, 3).Value = "S") Then latitude = latitude * (-1)

For i = 3 To nb_last_row
With Worksheets("Table")
.Range("C" & i & "").Value = Convert_water_depth_pressure_to_meter(CDbl(.Range("C" & i & "").Value), latitude)
' Delete all rows that have dept < 0.5 m
If .Cells(i, 3).Value < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows with velocity < 1450 (normal values are between 1450 and 1550)
If .Cells(i, 2).Value < 1450 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If

If i = nb_last_row Then Exit For
End With
Next i
End If```

All I am doing is adding my bit at the end (excuse the correction as I had the wrong cells in my original post)..

``` ' Calculate the latitude value in DD.ddddd needed to convert dbar in meters for water depth
latitude = Worksheets("Main menu").Cells(22, 2).Value * 24
If (Worksheets("Main menu").Cells(22, 3).Value = "S") Then latitude = latitude * (-1)

For i = 3 To nb_last_row
With Worksheets("Table")
.Range("C" & i & "").Value = Convert_water_depth_pressure_to_meter(CDbl(.Range("C" & i & "").Value), latitude)
' Delete all rows that have dept < 0.5 m
If .Cells(i, 3).Value < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows with velocity < 1450 (normal values are between 1450 and 1550)
If .Cells(i, 2).Value < 1450 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
' Delete all rows where difference in depth is not 0.5m
If (.Cells(i, 3).Value - .Cells(i - 1, 3).Value) < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
If i = nb_last_row Then Exit For
End With
Next i
End If```

--

Dave Peterson

2012년 6월 11일 월요일 오전 10:20
• ```  If IsNumeric(.Cells(1, 3).Value) And IsNumeric(.Cells(i - 1, 3).Value) Then
'do the calculation and possible delete
' Delete all rows where difference in depth is not 0.5m
If (.Cells(i, 3).Value - .Cells(i - 1, 3).Value) < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
Else
'do something else -- warning or what
MsgBox "Isn't Numeric"
End If```

OK slightly confused...  as you can see from above I put a trap in if it was not numeric... and sure enough the message box popped up! But then it continued on and performed the calculation anyway :/

I guess I'm on the right track, but thoroughly confused....

2012년 6월 12일 화요일 오전 7:18

MsgBox "Isn't Numeric around row# " & i
'then quit so you can check it
Exit Sub

It doesn't look like you implemented the first suggestion -- starting from the
bottom and working your way up.  It really makes life easier if you do that.

marky9074 wrote:

```    If IsNumeric(.Cells(1, 3).Value) And IsNumeric(.Cells(i - 1, 3).Value) Then
'do the calculation and possible delete
' Delete all rows where difference in depth is not 0.5m
If (.Cells(i, 3).Value - .Cells(i - 1, 3).Value) < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
nb_last_row = nb_last_row - 1
i = i - 1
End If
Else
'do something else -- warning or what
MsgBox "Isn't Numeric"
End If```

OK slightly confused...  as you can see from above I put a trap in if it was not numeric... and sure enough the message box popped up! But then it continued on and performed the calculation anyway :/

I guess I'm on the right track, but thoroughly confused....

--

Dave Peterson

2012년 6월 12일 화요일 오전 10:58
• Hi Dave,

I checked the row and it was #3, then I flipped it around as in the first suggestion, and still it shows #3, but it is still doing the calculation! Here is what I have now:

```     ' Calculate the latitude value in DD.ddddd needed to convert dbar in meters for water depth
latitude = Worksheets("Main menu").Cells(22, 2).Value * 24
If (Worksheets("Main menu").Cells(22, 3).Value = "S") Then latitude = latitude * (-1)

For i = nb_last_row To 3 Step -1
With Worksheets("Table")
.Range("C" & i & "").Value = Convert_water_depth_pressure_to_meter(CDbl(.Range("C" & i & "").Value), latitude)
' Delete all rows that have dept < 0.5 m
If .Cells(i, 3).Value < 0.5 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
End If
' Delete all rows with velocity < 1450 (normal values are between 1450 and 1550)
If .Cells(i, 2).Value < 1450 Then
.Cells(i, 2).EntireRow.Delete shift:=xlShiftUp
End If
If IsNumeric(.Cells(i, 3).Value) And IsNumeric(.Cells(i - 1, 3).Value) Then
' do the calculation and possible delete
' Delete all rows where sample is not > 0.25m
If Abs(.Cells(i, 3).Value - .Cells(i - 1, 3).Value) < 0.25 Then
.Cells(i, 3).EntireRow.Delete shift:=xlShiftUp
End If
Else
'do something else -- warning or what
MsgBox "Isn't numeric around row # " & i
End If
If i = 3 Then Exit For
End With
Next i
End If```

2012년 6월 13일 수요일 오전 3:37
• OK looks like I have been royally dumb as you were both right!

I was trying to subtract a text cell at row 2 hence why it was returning the error... and then because I was going from top to bottom instead of bottom to top it bailed out at the first line...

Not sure how I can mark both answers as right, but sure someone will come and clean this up.

Thanks again.

Mark

2012년 6월 13일 수요일 오전 3:51