- Jan 7, 2019
- Office Version
Greetings, I would like to follow up on a VBA that works flawlessly. The following VBA swaps two possible 3 three letter codes, and replaces them with another three letter code found in another column. After this process is done, a VLOOKUP is completed with VBA (this macro is already complete and works great). What I need to happen then is to take if applicable one of the two three letter codes that were replaced and place in front of the new code with a dash (-). For example I had BGR in Column H the macro looked in Column X and placed "CHS" where the BGR was in Column H. Then after the VLOOKUP is run, place "BGR-" in front in the same cell as the new code. In this case the end result would be "BGR-CHS". This has to be done after the VLOOKUP and before I delete my redundant Columns. I figured since Column V is open, we can place the BGR or YQX temporarily there, and with the new VBA, take it from there and place it front of the code (same cell) in Column H.
Public Sub downline() Dim oWs As Worksheet, rng As Range, c As Range Set oWs = ThisWorkbook.Sheets("72 hr") Set rng = Application.Intersect(oWs.[H:H], oWs.UsedRange) For Each c In rng If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then c.Value = Mid(c.Offset(0, 16).Value, 5, 3) End If Next c End Sub