Do a calculation whilst in a loop Excel 2003
-
2012년 6월 10일 일요일 오전 5:32
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
- 편집됨 marky9074 2012년 6월 10일 일요일 오전 5:32
모든 응답
-
2012년 6월 10일 일요일 오전 7:17답변자
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 i
End With
Ed Ferrero
www.edferrero.com- 편집됨 Ed FerreroEditor 2012년 6월 10일 일요일 오전 7:17
-
2012년 6월 10일 일요일 오전 9:20
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일 일요일 오전 10:36
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 ifuntested, 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
- 답변으로 표시됨 Max MengMicrosoft Contingent Staff, Moderator 2012년 6월 21일 목요일 오전 2:40
-
2012년 6월 11일 월요일 오전 3:29
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..
Existing code before my addition:
' 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 IfAll 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일 월요일 오전 10:20
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..
Existing code before my addition:' 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 IfAll 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월 12일 화요일 오전 7:18
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 IfOK 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일 화요일 오전 10:58
Add some info to your msgbox:
MsgBox "Isn't Numeric around row# " & i
'then quit so you can check it
Exit SubIt 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 IfOK 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월 13일 수요일 오전 3:37
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:51
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
.png)
