none
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



    • 편집됨 marky9074 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 i
    End 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..

    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 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



    • 편집됨 marky9074 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..
    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 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
  • Add some info to your msgbox:

    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