How do I create simple arithmetic in VBA?

[oliviar]

Hi Guys,
I want to make a VBA loop until the value in column B is blank.
I want it to do this calculation in K only if there is a value in column F:

if G+ H - I < .5 then "yes"
if G + H - I not < .5 then "no"

I could maybe work out the loop, but I don't know how to do simple maths in vba! Lame... i know.

 

[Leith Ross]

Hello oliviar,

Here is one method:

Sub LoopMacro()

  Dim Cell As Range
  Dim Rng As Range
  
    Set Rng = Range("B1", Cells(Rows.Count).End(xlUp))
    
      For Each Cell In Rng
        If Cell.Offset(0, 4) <> "" Then
          If Cell.Offset(0, 6) + Cell.Offset(0, 7) - Cell.Offset(0, 8) < 0.5 Then
             Cell.Offset(0, 10) = "yes"
          Else
             Cell.Offset(0, 10) = "no"
          End If
        End If
      Next Cell
      
End Sub

Sincerely,
Leith Ross

 

[oliviar]

I get an error that says type mismatch on this line:

  If Cell.Offset(0, 6) + Cell.Offset(0, 7) - Cell.Offset(0, 8) < 0.5 Then

 

[Leith Ross]

Hello Oliviar,

I think I counted my offsets wrong. Try this...

Sub LoopMacro()

  Dim Cell As Range
  Dim Rng As Range
  
    Set Rng = Range("B1", Cells(Rows.Count).End(xlUp))
    
      For Each Cell In Rng
        If Cell.Offset(0, 4) <> "" Then
          If Cell.Offset(0, 5) + Cell.Offset(0, 6) - Cell.Offset(0, 7) < 0.5 Then
             Cell.Offset(0, 9) = "yes"
          Else
             Cell.Offset(0, 9) = "no"
          End If
        End If
      Next Cell
      
End Sub

Sincerely,
Leith Ross

 

[oliviar]

Hey Leith,
The offsets are perfect, VB seems to be taking issue with the + - and <

 

[Leith Ross]

Hello Oliviar,

The only reason I can think of why VBA would object is the cells don't contain number values. If the values are text then they need to be converted, like below..

      For Each Cell In Rng
        If Cell.Offset(0, 4) <> "" Then
          If CDbl(Cell.Offset(0, 5)) + CDbl(Cell.Offset(0, 6)) - CDbl(Cell.Offset(0, 7)) < 0.5 Then
             Cell.Offset(0, 9) = "yes"
          Else
             Cell.Offset(0, 9) = "no"
          End If
        End If
      Next Cell

Sincerely,
Leith Ross

 

[oliviar]

They are dates and times, but they are valid according to excel, and I don't care how they are represented in the end. I am trying to find the values that are separated by less than half a day, hence the .5