VBA - change value of cell depening on condition

Question

seems like a simple enough question, but I couldn't find the answer here. I've used StackOverflow sooo much already the last year and finally decided to ask a question myself, I'm sure someone knows the answer!

The situation: I have a column of values with unique filenames and if they were processed or not. Eg: "20160810_123_a.xml processed" or "20160810_123_b.xml failed" The length of the filename changes so it's not static. I'm using these values in a different report and would like only values of 'Processed' or 'Failed', depending on what was in the original content.

I'm guessing I need some kind of If loop, which would do something like this: In range I:I, if value of the cell contains "processed", replace entire cell with "Processed". If the value of the cell contains "failed", replace entire cell with "Failed.

Thanks in advance!

You could use a simple formula to do that. Does it need to be VBA? — Brian, Oct 14 '16 at 11:57
Hey Brian, I'm using VBA to process the entire document, this is one step of it. So yeah. I could always put a formula in there of course. What would you suggest? — Intuos, Oct 14 '16 at 12:01
There are a few options here but if you only want to change the case so that you can perform a comparison in VBA, you can just use `LCase` or `UCase` in your VBA code when you do the comparison. That way regardless of the case in the string, you will get a correct result — Zac, Oct 14 '16 at 12:18
I'm not trying to change the case here, that's irrelevant really. Sorry if that came across as such. I just want to search if a cell meets a criteria (the filename contains "failed" or "processed") and change the entire cell value to "failed" or "processed", depending on which one was found. — Intuos, Oct 14 '16 at 12:29

score 3 · Accepted Answer · answered Oct 14 '16 at 13:02

3

try this

Sub main()
    With Worksheets("Conditions") '<--| change "Conditions" to your actual data sheet
        With .Range("I1", .Cells(.Rows.Count, "I").End(xlUp)).SpecialCells(XlCellType.xlCellTypeConstants) '<--| change "I"s to your actual column index where to search for processed/failed
            .Replace what:="*processed*", replacement:="Processed", LookAt:=xlWhole, MatchCase:=False
            .Replace what:="*failed*", replacement:="Failed", LookAt:=xlWhole, MatchCase:=False
        End With
    End With
End Sub

answered Oct 14 '16 at 13:02

user3598756

28,893
4
18
28

That's a really nice way to go about it! – Brian Oct 14 '16 at 13:23
Thank you @Brian. Lets' see what Intuos thinks about that! – user3598756 Oct 14 '16 at 13:33
Works like a charm! I've already entered this comment a couple of times but it seems to diseappear, thanks a lot! – Intuos Oct 19 '16 at 07:32
you are welcome. as for the comment _disappearing_, be informed that comments have a minimum required length of 15 characters – user3598756 Oct 19 '16 at 07:39

score 0 · Answer 2 · answered Oct 14 '16 at 12:24

0

This worked for me:

Option Explicit

Sub Test()

Dim arr() As Variant
Dim i As Long
Dim myRange As Range
Dim wf As WorksheetFunction

Set wf = Application.WorksheetFunction
With ActiveSheet
    Set myRange = .Range(.Cells(1, 1), Cells(.Rows.Count, 1).End(xlUp))
End With

arr = myRange
For i = 1 To UBound(arr)
    arr(i, 1) = wf.Proper(Right(arr(i, 1), Len(arr(i, 1)) - wf.Find(" ", arr(i, 1))))
Next i
myRange.Offset(0, 1) = arr

End Sub

answered Oct 14 '16 at 12:24

Brian

2,078
1
15
28

Thanks, I was gonna try this, but my mind started boggling after reading this bit and saw user3598756's response, which worked :) – Intuos Oct 14 '16 at 14:06

VBA - change value of cell depening on condition

2 Answers2