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".
-
3This sounds like something you can do with formula, must it be in VBA? Also, is it correct to categorise 3,000 as "more than 3k"?Raymond Wu– Raymond Wu2021-08-04 07:59:16 +00:00Commented Aug 4, 2021 at 7:59
-
Yeah it can be done just using IF formula, but I've been told that I can also achieve the same result using vba, but I'm still not sure how should I code that in vba to get the same result.helpme777– helpme7772021-08-06 04:47:33 +00:00Commented Aug 6, 2021 at 4:47
-
Ermm normally people won't even go to the route of VBA if the entire thing can be done via Formula. I'll post one using VBA for your reference then.Raymond Wu– Raymond Wu2021-08-06 04:54:45 +00:00Commented Aug 6, 2021 at 4:54
Add a comment
|
2 Answers
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.
1 Comment
helpme777
Yeah thank you for the tips. I also have done this using the same method as you mentioned, but I am just trying to explore more using vba and I have been told that I can also get the same result using vba but im still not sure how to code it. Anyways, thank you for the great reply.
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
