Inserting text that previously was replaced, after VLOOKUP is run.

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
743
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You don't need a temporary column for that, you can join the new string to the existing one instead of overwriting it in your existing code.
VBA Code:
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 = c.Value & "-" & Mid(c.Offset(0, 16).Value, 5, 3)
        End If
    Next c
End Sub
 
Upvote 0
Solution
You don't need a temporary column for that, you can join the new string to the existing one instead of overwriting it in your existing code.
VBA Code:
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 = c.Value & "-" & Mid(c.Offset(0, 16).Value, 5, 3)
        End If
    Next c
End Sub
I’m not near a computer now, but I would just add this macro after the VLOOKUP? Totally makes sense. I’ll give ago and let you know.
 
Last edited:
Upvote 0
I'm not seeing how the vlookup part relates to the rest, what is being looked up from where?

Having read your first post again, I'm not sure that what I've suggested will work for you but if I can understand what you want to do with the lookup then I should be able to fit it all together.
 
Upvote 0
I'm not seeing how the vlookup part relates to the rest, what is being looked up from where?

Having read your first post again, I'm not sure that what I've suggested will work for you but if I can understand what you want to do with the lookup then I should be able to fit it all together.
Thank you for your help and patience. First of all your code works perfect for adding the follow on terminal to the terminal code in H. However, The image below shows the order in which my macros are called up. The macro downline will replace the entry GBR or YQX (normally a refueling stop) with the following station. That following station is then substituted for GBR or YQX. This is where the VLOOKUP comes into play. The VLOOKUP is used and the list which has over 10K rows. The VLOOKUP has to be a single three letter code that is why I need just the one code (follow on terminal). Once the VLOOKUP is done then that is where I would need the GBR or YQX placed in front of the follow on base for instance “GBR-FRF”. I provided my VLOOKUP macro, and that works perfectly, but it won't if I have a "GBR- or YQX-" in front. I don't think I can change the VLOOKUP macro to look for instances where there are is more than the 3 letter code.


VBA Code:
Private Sub VLOOK_UP()
    Dim i As Integer
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        Worksheets("72 Hr").Cells(i, 12).Value = _
        Application.WorksheetFunction.VLookup(Worksheets("72 Hr").Cells(i, 8).Value, _
        Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub


Refueling stop.JPG
 
Upvote 0
I'm not seeing how the vlookup part relates to the rest, what is being looked up from where?

Having read your first post again, I'm not sure that what I've suggested will work for you but if I can understand what you want to do with the lookup then I should be able to fit it all together.
hello, I got good news I got it sorted out now. I just changed the VBLOOKUP and it works perfectly with the macro you provided. Thank you so much. Here is my new macro for the VLOOKUP.

VBA Code:
Private Sub VLOOK_UP()
    Dim i As Integer
    For i = 1 To Split(Worksheets("72 Hr").UsedRange.Address, "$")(4)
        Worksheets("72 Hr").Cells(i, 12).Value = _
        Application.WorksheetFunction.VLookup(Right(Worksheets("72 Hr").Cells(i, 8).Value, 3), _
Worksheets("3 LTR").Range("A:B"), 2, 1)
    Next i
End Sub
 
Upvote 0
hello, I got good news I got it sorted out now.
Well done you :)

I didn't get chance to respond last night after you added the extra information, it is always nice to see when posters have continued with their own efforts and been able to resolve their own questions instead of sitting back and waiting for an answer.

Looking at the information you provided, this is how I would have done it, your vlookup macro can be done by adding one line to the downline macro. It is rarely necessary to add temporary data to the sheet for a macro to be processed and doing so is one of the many causes for the code to run slower.
VBA Code:
Public Sub downline_vlookup()
    Dim oWs As Worksheet, rng As Range, c As Range

    Set oWs = ThisWorkbook.Sheets("72 hr")
    Set rng = Application.Intersect(oWs.Range("H:H"), oWs.UsedRange)

    For Each c In rng
        If CBool(InStr("|BGR|YQX|", "|" & c.Value & "|")) Then
            c.Value = c.Value & "-" & Mid(c.Offset(0, 16).Value, 5, 3)
            c.Offset(0, 4).Value = Evaluate("=VLOOKUP(" & Right(c.Value, 3) & "'3 LTR'!A:B,2)")
        End If
    Next c
End Sub
I would suspect that many of your 40 or so procedures (macros) could be condensed in similar ways, although I would advise not trying to do too much with it if you want to be able to make changes yourself. While it might be possible to do what you want with a lot less code and in a lot less time, there could be things that you might not understand should you need to alter it, which may mean waiting for help instead of a quick fix. I know for certain that there are better methods than the ones that I use for some things, but I tend to stick with what I know works and that I can fix when I need to.
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top