How to use if function using VBA

Question

How to code in VBA to come up with status of the value in different column?

I want the VBA to come up with the status of value in column B "more than 5k, more than 3k, low value".

Answer 1

If you really want to do it via VBA (and not use formula):

Private Sub CategoriseValue()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1") 'change to the correct sheet name
    
    Const startRow As Long = 1
    Const endRow As Long = 8
    Const srcColumn As Long = 2 'Column B
    Const resultColumn As Long = 3 'Column C
    
    Dim srcArr As Variant
    srcArr = ws.Range(Cells(startRow, srcColumn), Cells(endRow, srcColumn)).Value
    
    Dim resultArr() As String
    ReDim resultArr(1 To UBound(srcArr), 1 To 1) As String
    
    Dim i As Long
    For i = LBound(srcArr, 1) To UBound(srcArr, 1)
        Dim result As String
        
        If IsNumeric(srcArr(i, 1)) Then
            Select Case srcArr(i, 1)
                Case Is > 5000: result = "more than 5k"
                Case Is > 3000: result = "more than 3k"
                Case Else: result = "low value"
            End Select
        Else
            result = "NA"
        End If
        
        resultArr(i, 1) = result
    Next i
    
    ws.Range(Cells(startRow, resultColumn), Cells(endRow, resultColumn)).Value = resultArr
    
End Sub

Answer 2

I'd recommend achieving this with formulas instead of VBA, because it'll be easier, more people could edit it, there's no security concerns, etc.

Assuming that your 10000 is in cell B1, you can put this in C1:

=IF(ISNUMBER(B1),IF(B1>5000,"more than 5k",IF(B1>3000,"more than 3k","low value")),"NA")

That will return:

• "NA" if the cell contains text, an error or is blank.
• "more than 5k" if the number is above 5000.
• "more than 3k" if the number is above 3000.
• "low value" for 3000 or below. So that includes negative numbers.