Replacing data in one cell with second name found in another cell

[Livin404]

Greetings,
I'm hoping someone might be able to figure out how to replace a three letter acronym in one cell with the second acronym found in another through VBA. In the example I'm looking to replace BGR found in Column G with LAX only (LAX) found in Column N. There will be no dashes nor space when LAX replaces BGR. This would run true for the entire worksheet "72 Hr" worksheet. LAX is just an example there can be thousands of different three letter codes found is the second code in Column N. If there is no second three letter code then just ignore. In addition, YQX is the only other code in Column G I would like this process to work for. This has to be accomplished for the next VBA I have is to delete all Columns including Column N and conduct a VLOOKup through VBA. Both of those I already have completed. Thank you,

 

[mrshl9898]

Try:

Sub replaceinitials()

Dim rownum As Long

rownum = 1

Do Until Cells(rownum, 7) = ""
    If Len(Cells(rownum, 14)) > 3 Then
        Cells(rownum, 7) = Mid(Cells(rownum, 14), 5, 3)
    End If
rownum = rownum + 1
Loop


End Sub

 

[GWteB]

As an alternative to mrshl9898's code ...

Public Sub Livin404()

    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 = Mid(c.Offset(0, 7).Value, 5, 3)
        End If
    Next c
End Sub

 

[Livin404]

As an alternative to mrshl9898's code ...

Public Sub Livin404()

    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 = Mid(c.Offset(0, 7).Value, 5, 3)
        End If
    Next c
End Sub

Thank you for the quick response. May I ask what are those verticle lines are starting on line If CBool(inStr... and is the 7 seven columns over from Column G?

 

[GWteB]

You are welcome. Any question can be asked as far as I am concerned ( ... it will become clear in time whether someone will answer it).

Those vertical lines are pipe symbols and used as necessary and (almost) unique delimiters within this statement.
This line of code does the following:
- the desired cell (c) is read and the value found (c.Value) is placed between those pipe symbols;
- the resulting string ( |BGR| or |YQX| or ??? or whatever) is compared against the custom string "|BGR|YQX|" in a way whether the resulting string occurs within that custom string or not;
- the InStr function returns a number, specifying the position of the first occurrence of the "pipe surrounded" string (c.Value) within the custom string;
- in case that string does not occur within the custom string, a 0 (zero) is returned;
- the result is then converted into a Boolean variable using the CBool function: 0 equals FALSE, any other number equals TRUE.
- so both |BGR| and |YQX| result in TRUE, any onther value in c.Value results in FALSE.

The 7 represents the offset from column G since variabele c represents either a cell in (no other column but) column G or "nothing".
An offset of 1 results in column H, an offset of 7 results in column N.

 

[Livin404]

Try:

Sub replaceinitials()

Dim rownum As Long

rownum = 1

Do Until Cells(rownum, 7) = ""
    If Len(Cells(rownum, 14)) > 3 Then
        Cells(rownum, 7) = Mid(Cells(rownum, 14), 5, 3)
    End If
rownum = rownum + 1
Loop


End Sub

Thank you for your response, I used your formula and put in the actual column numbers. I reduced it just so it will fit in a picture. When I ran it I got an error message, "Run-Time error '1004': Application-Defined or object defined error". There was some activity in Column 8. Some three letter codes just disappeared, the two that needed changing did change. A couple of them who happened to have BGR as the second code in Column 24 were placed in Column 8. Also how would I also include YQX in the formula. Than you so much. The following is the VBA I used.

Sub replaceinitials()

Dim rownum As Long

rownum = 1

Do Until Cells(rownum, 8) = "BGR"
    If Len(Cells(rownum, 24)) > 3 Then
        Cells(rownum, 8) = Mid(Cells(rownum, 24), 5, 3)
    End If
rownum = rownum + 1

Loop

End Sub

 

[mrshl9898]

Not sure about the error. What are some examples of text found in column 24?

I was assuming you'd have 123 or 123-123 or 123-123-123 only. It sees if there are more than 3 characters and if so it will replace column 8 with the 5th to 7th characters (eg 123-456 or 123-456-789 would return 456)

The code will now stop when it gets to BGR, it was supposed to stop when it found a blank cell ""

 

[Livin404]

You are welcome. Any question can be asked as far as I am concerned ( ... it will become clear in time whether someone will answer it).

Those vertical lines are pipe symbols and used as necessary and (almost) unique delimiters within this statement.
This line of code does the following:
- the desired cell (c) is read and the value found (c.Value) is placed between those pipe symbols;
- the resulting string ( |BGR| or |YQX| or ??? or whatever) is compared against the custom string "|BGR|YQX|" in a way whether the resulting string occurs within that custom string or not;
- the InStr function returns a number, specifying the position of the first occurrence of the "pipe surrounded" string (c.Value) within the custom string;
- in case that string does not occur within the custom string, a 0 (zero) is returned;
- the result is then converted into a Boolean variable using the CBool function: 0 equals FALSE, any other number equals TRUE.
- so both |BGR| and |YQX| result in TRUE, any onther value in c.Value results in FALSE.

The 7 represents the offset from column G since variabele c represents either a cell in (no other column but) column G or "nothing".
An offset of 1 results in column H, an offset of 7 results in column N.

Thank you, I used the following VBA and though I'm not getting any errors nothing seems to happen. I made a couple changes but only to the columns. I condensed it to fit in the image. Actually the BGR & YQX are in Column H while the list where I may find the second three letter code is in Column X.

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

You are welcome. Any question can be asked as far as I am concerned ( ... it will become clear in time whether someone will answer it).

Those vertical lines are pipe symbols and used as necessary and (almost) unique delimiters within this statement.
This line of code does the following:
- the desired cell (c) is read and the value found (c.Value) is placed between those pipe symbols;
- the resulting string ( |BGR| or |YQX| or ??? or whatever) is compared against the custom string "|BGR|YQX|" in a way whether the resulting string occurs within that custom string or not;
- the InStr function returns a number, specifying the position of the first occurrence of the "pipe surrounded" string (c.Value) within the custom string;
- in case that string does not occur within the custom string, a 0 (zero) is returned;
- the result is then converted into a Boolean variable using the CBool function: 0 equals FALSE, any other number equals TRUE.
- so both |BGR| and |YQX| result in TRUE, any onther value in c.Value results in FALSE.

The 7 represents the offset from column G since variabele c represents either a cell in (no other column but) column G or "nothing".
An offset of 1 results in column H, an offset of 7 results in column N.

I got it to work. I copied my data from a secured website, so I had to use the following VBA to make your VBA work. Thank you.

Sub remove_hidden_Values()
Dim cell As Range
    For Each cell In Selection
        cell.Value = Trim(Replace(cell.Value, Chr(160), Chr(32)))
    Next
End Sub

 

[Livin404]

Not sure about the error. What are some examples of text found in column 24?

I was assuming you'd have 123 or 123-123 or 123-123-123 only. It sees if there are more than 3 characters and if so it will replace column 8 with the 5th to 7th characters (eg 123-456 or 123-456-789 would return 456)

The code will now stop when it gets to BGR, it was supposed to stop when it found a blank cell ""

I got it to work. I had to use another VBA to clean up the cell which data I cut and pasted from an online secured database. Thank you so much for the input.

Sub remove_hidden_Values()
Dim cell As Range
    For Each cell In Selection
        cell.Value = Trim(Replace(cell.Value, Chr(160), Chr(32)))
    Next
End Sub

 

[GWteB]

You are welcome and thanks for letting us know.
Glad you were able to make the necessary adjustments in order to be able to use the code under changed circumstances.