Copying data from one column and moving that data in front of data in another column. (Only if specific criteria is met).

[Livin404]

Greetings, in Column G I will have a list of airport codes (3 letters), and in Column U I will have anther a list of three letter codes to indicate the routing. I would like only when the Codes "BGR" or "YQX" appear in Column G to immediately search the same row in Column U and select the the letter code before the "BGR" or "YQX" at the end of Column U. For example with CHS-HUA-OZP-SDA-OZP-BGR in Column U, I would have the macro go to the end and select the three letter code before the BGR and place it after "BGR" in Column G preceded by a dash. The end result in Column G would be BGR-OZP.
I have a Macro that I think is getting me close but it is erroring out at the c.Value=.... line. Thank you,

Public Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
             c.Value = c.Value & "_" & Mid(c.Offset(0, 14).Value, -4, 3)
                End If
         Next c
End Sub

 

[Michael M]

Maybe this way

ublic Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = c & " - " & Mid(c.Offset(0, 14).Value, 17, 3)
                End If
         Next c
End Sub

 

[Livin404]

Maybe this way

ublic Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c I
         Next c
End Sub

In the example I displayed( CHS-HUA-OZP-SDA-OZP-BGR is just an example. The flight routing will not always have all those amount of three letter codes it can be as little as HUA-OZP-BGR. If “BGR” or “YQX” show up in Column G then the same three letter code will be at the end of Column U whether it is just Iwo codes like MHZ-BGR or eight codes. Thank you.

Maybe this way

ublic Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = c & " - " & Mid(c.Offset(0, 14).Value, 17, 3)
                End If
         Next c
End Sub

I see the number 17 does that

 

[Michael M]

In that case can't you simply use the last 7 characters in Column "U" if the criteria is met

Public Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = Right(c.Offset(0, 14).Value, 7)
                End If
         Next c
End Sub

 

[Livin404]

In that case can't you simply use the last 7 characters in Column "U" if the criteria is met

Public Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = Right(c.Offset(0, 14).Value, 7)
                End If
         Next c
End Sub

That totally makes since I’ll try in the morning. Thank you.

 

[Livin404]

In that case can't you simply use the last 7 characters in Column "U" if the criteria is met

Public Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = Right(c.Offset(0, 14).Value, 7)
                End If
         Next c
End Sub

Hello and thank you for the input, and sorry for the delayed response. I sometimes have a demanding work cycle. I did try the latest suggestion and unfortunately it didn't quite meet my desired outcome. What I'm looking for is a bit more tricky than I thought. I included a photo to illustrate the results I'm looking for. The same result I would also like for the code YQX. Thank you so much.

 

[Livin404]

In that case can't you simply use the last 7 characters in Column "U" if the criteria is met

Public Sub downlinE_In()
    Dim oWs As Worksheet, rng As Range, c As Range
    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.[G:G], oWs.UsedRange)
    For Each c In rng
       If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c = Right(c.Offset(0, 14).Value, 7)
                End If
         Next c
End Sub

It is working properly with this code, than you for your help

Sub downLineE_In()
Dim oWs As Worksheet
Dim Rng As Range, c As Range
Set oWs = ThisWorkbook.Sheets("72 hr")
With oWs
        Set Rng = Application.Intersect(.[G:G], .UsedRange)
        
  For Each c In Rng
        If Trim(UCase(c.Value)) = "BGR" Or Trim(UCase(c.Value)) = "YQX" Then
                If InStr(.Range("U" & c.Row).Value, "BGR") > 0 Or InStr(.Range("U" & c.Row).Value, "YQX") > 0 Then
                        .Range("G" & c.Row).Value = c.Value & "-" & Left(Right(.Range("U" & c.Row).Value, 7), 3)
                Else
                        .Range("G" & c.Row).Value = c.Value & "-" & Right(.Range("U" & c.Row).Value, 3)
                End If
        End If
 Next c
 End With
End Sub

 

[Michael M]

Glad you fixed it...