I have an excel sheet with multiple columns, One column has reference(s) to previous (just like in MS-Project),
I am trying to get the row numbers for each of the reference(s) using the below function, this works well.
Public Function Splitter() As String
Dim multiRef() As String
Dim ws As Worksheet, rowNumber As Integer
Dim multiValue As String
Dim rowIndex As String, tempValue As String
Set ws = Worksheets("Sheet1")
rowNumber = ActiveCell.row
multiValue = ws.Range("T" & rowNumber).Value
multiRef = Split(multiValue, ";")
For i = 0 To UBound(multiRef)
If ((StrComp(Trim(multiRef(i)), "-") = 0) Or (StrComp(Trim(multiRef(i)), "Applicable") = 0) Or (StrComp(Trim(multiRef(i)), "") = 0) Or (StrComp(Trim(multiRef(i)), "TBD") = 0) Or (StrComp(Trim(multiRef(i)), "Y") = 0)) Then
Else:
tempValue = Application.WorksheetFunction.Match(Trim(multiRef(i)), ws.Range("E2:E1500"), 0)
If (Not IsNull(tempValue)) Then
rowIndex = rowIndex & tempValue & ", "
End If
End If
Next i
If (Len(rowIndex) <= 0) Then
Splitter_System = ""
Else: Splitter_System = Left(rowIndex, Len(rowIndex) - 2)
End If
End Function
The problem is that when I copy the same to the below cells, it just copies the same content, and unless I manually edit each row the data is not updated.
Is there an easier way to update the value automatically?