subtracting one cell from another using vba

[vba317]

I am trying to write a macro to subtract one cell from another. If d5 is less than d4 I want cell G4 to have a message Low = A leg. I also want the number to be put in cell H4.

Sub FibNumbers()

    Dim i As Long
    Dim LR As Long
    Dim C(600) As Long
    'Dim D(600) As Long
    Dim D As String
    Dim G(600) As Long
    Dim ALegLowL As Double
    Dim ALegLowV As Double
    Dim ALegLowV1 As Double
    Dim ALegLowV2 As Double
    Dim ALegLowV3 As Double
    
    
     LR = ActiveSheet.UsedRange.Rows.Count
        
     For i = 2 To LR
        
     'If C(i) < C(i + 1) Then Range(G(i)).Value = C(i)
        
    
    
    ' Min Value Calculation
        'A leg Low location
        ALegLowL = i
        'A leg low header
         If Range("D" & i + 1 - "D" & i < 0) Then Range("G" & ALegLowL).Value = "Low = A leg"
        'A leg Value
         'AleglowV
        ALegLowV1 = i
        If Range("D" & i + 1 - "D" & i < 0) Then ALegLowV2 = WorksheetFunction.Min(Range(Cells(ALegLowV1, "D"), Cells(LR, "D")))
                If Range("D" & i + 1 - "D" & i < 0) Then Range("H" & ALegLowL).Value = ALegLowV2
        
        Next i
End Sub

 

[SkipVought]

Hi,

I'm not sure what you mean, but this makes no sense as an expression...

If Range("D" & i + 1 - "D" & i < 0) Then

Maybe...

If Range("D" & i + 1 ) < 0 Then

Why is the "D" in ther TWICE?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

or is it...

If Range("D" & i + 1) - Range("D" & i) < 0 Then

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

I am trying to create a loop that loops through column D. the small letter i is the counter. I want the procedure to run when D5-D4 is a negative number. I thought the ("D" & i + 1) formula would work when i =5 . Your right, my thinking is wrong, which is why my program is not working. Can I resolve this problem?

Tom

 

[SkipVought]

Isn't that what I coded in my previous post?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[vba317]

Yes I just tried your suggestion. You did provide me with an answer to my question. I asked the wrong question thou. If many cells in a row go down I want the last cell to be displayed not all the inbetween cells. I guess I will have to try and use the min fumction, I'm not sure how thou.

If Range("D" & i + 1) - Range("D" & i) < 0 Then this seemed to work

 

[SkipVought]

I want the last cell to be displayed

MsgBox "Last value is " & Cells(1,"D").End(xlDown).Value

Assuming that D1 is the first cell with data and data is contiguous

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}