Hi team,
I am unsure if my problem can be solved using Excel VBA, however I will give it a try.
I have a really long formula as below.
My ultimate goal is to make this formula short.
The formula is written to write Yes if Last 3 digit numbers of Table Number and the Team color is matching.
(For example, as per the table in Data Tab, Team Color must be Blue if last 3 digit numbers of Table Number is 135. Team Color must be Green if last 3 digit numbers of Table Number is 118.)
Can any VBA Master help me to achieve below:
- I want to use the table in Data Tab, instead of keep making the formula long and big.
- I want to apply this formula whenever I update the table in Data Tab (I can add/delete/update numbers and Team Colors in future.)
Below is my current code, and I am attaching the file for your attention.
Code
Sub Formula()
'Formula
Dim LastRowColumnB As Long
Dim BlueVar As Variant
LastRowColumnB = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Table").Range("C2:C" & LastRowColumnB).Formula = _
"=IF(OR(AND(C[-2]=""Blue"",OR(ISNUMBER(SEARCH(""135"",C[-1])),ISNUMBER(SEARCH(""136"",C[-1])),ISNUMBER(SEARCH(""137"",C[-1])),ISNUMBER(SEARCH(""138"",C[-1])),ISNUMBER(SEARCH(""139"",C[-1])),ISNUMBER(SEARCH(""140"",C[-1]))))," & _
"AND(C[-2]=""Purple"",ISNUMBER(SEARCH(""439"",C[-1])))," & _
"AND(C[-2]=""Yellow"",ISNUMBER(SEARCH(""412"",C[-1])))," & _
"AND(C[-2]=""Green"",ISNUMBER(SEARCH(""118"",C[-1])))," & _
"AND(C[-2]=""Red"",ISNUMBER(SEARCH(""090"",C[-1]))))," & _
"""Yes"",""No"")" & _
""
' message Box
MsgBox "Completed!"
End Sub
Display More
Thanks!