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

#### Livin404

##### Active Member
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### jasonb75

##### Well-known Member
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``````

#### Livin404

##### Active Member
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:

#### jasonb75

##### Well-known Member
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.

#### Livin404

##### Active Member

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``````

#### Livin404

##### Active Member
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``````

#### jasonb75

##### Well-known Member
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.

Replies
7
Views
89
Replies
22
Views
452
Replies
0
Views
103
Replies
5
Views
163
Replies
0
Views
52

1,127,328
Messages
5,624,036
Members
416,007
Latest member
csf

### 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.

### Which adblocker are you using?

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

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