CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel condition-based negative values don't work

I would like to set up the different formula for negative and positive values.

My code below suppose to be valid, but only the formula for negative values work.

 Dim wks As Worksheet
 Dim sunString As String
 Dim rng3 As Range

 sunString = "-"

 Set wks = ThisWorkbook.ActiveSheet

 Set rng3 = wks.Range("F2:F" & lRow)

With rng3
  If InStr(sunString, "-") > 0 Then
    Range("W2:W" & lRow).Formula = "=R2-U2-V2"
  Else
    Range("W2:W" & lRow).Formula = "=R2+U2+V2"
  End If

enter image description here

The second formula (else statement) doesn't work at all. What is wrong here?

Upvotes: 0

Views: 255

Answers (1)

norie
norie

Reputation: 9857

Why not use a single formula that includes the condition and get rid of the unneeded looping?

Sub LoopRange()
Dim wks As Worksheet
Dim lRow As Long
Dim rng As Range

    Set wks = ThisWorkbook.ActiveSheet

    lRow = wks.Cells(wks.Rows.Count, "F").End(xlUp).Row
    
    Set rng = wks.Range("W2:W" & lRow)

    rng.Formula = "=IF(F2<0,R2-U2-V2 ,R2+U2+V2)"

End Sub

If the data in column F is not numeric you could use this formula to check for -.

rng.Formula = "=IF(LEFT(F2)=""-"",R2-U2-V2 ,R2+U2+V2)"

Upvotes: 1

Related Questions