The original functioning code by VBasic2008 in the accepted answer.
I thought I'd understood the code, and have often modified it as necessary without problem. I'm now having trouble with the following:
For r = 1 To rCount
rString = CStr(sData(r, 3))
If InStr(rString, "Completed") = 1 Then ' if cell r in column C has the string "Completed"
nIndex = Application.Match(rString, Faelle, 0)
If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
End If
Next r
I modified the code by shortening the loop to meet just one condition, and that condition is to check wether the cell does contain a specific string (not whether the cell is empty).
when I run the code, nothing happens. I don't get any error message, so I can't figure out why it's behaving like that.
Here's the complete sub:
Sub setStatus()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Worksheets(3)
Dim rg As Range: Set rg = ws.Range("A1").CurrentRegion
Dim rCount As Long: rCount = rg.Rows.Count
Dim srg As Range: Set srg = rg.Resize(rCount)
Dim sData() As Variant: sData = srg.Resize(, 3).Value
Dim dData() As Variant: ReDim dData(1 To rCount, 1 To 1)
Dim nIndex As Variant, r As Long, rString As String
Dim Faelle() As Variant: Faelle = VBA.Array("Status1", "Status2", "Status3", "Status4", "Status5")
Dim Werte() As Variant: Werte = VBA.Array(1, 2, 3, 4, 5)
For r = 1 To rCount
rString = CStr(sData(r, 3))
If InStr(rString, "Completed") = 1 Then ' if cell r in column C has the string "Completed"
nIndex = Application.Match(rString, Faelle, 0)
If IsNumeric(nIndex) Then dData(r, 1) = Werte(nIndex - 1)
End If
Next r
Dim drg As Range: Set drg = srg.Columns(6)
drg.Value = dData
End Sub
To clarify, the purpose of this sub is, having a condition met, match the value of the cell/first array with the values of the second array. The values of the second array should be inserted in the corresponding cell in column 6.
