Livin404
Well-known Member
- Joined
- Jan 7, 2019
- Messages
- 743
- Office Version
- 365
- 2019
- Platform
- Windows
Greetings I have two macros that work great using the VLOOKUP. The issue I have one will take the text from Column 8 and then the other from Column 9. Before I had a macro that removed all rows that had text in Column 8 and the other in Column 9 which worked great. However I need a VLOOKUP that will search the whole data base. Basically when there is text in Column 8 it will be blank in Column 9 and vice versa. Before would end up having two sheets of data. This new one will give me one. My Macros I hope to combine are below,
I think they can be combined but some sort of statement stating if there is nothing in Column 8 then to search in Column 9.
Thank you very much.
second code:
I think they can be combined but some sort of statement stating if there is nothing in Column 8 then to search in Column 9.
Thank you very much.
VBA Code:
Private Sub VLOOK_UP()
Dim i As Long, n As Variant
For i = 1 To Split(Worksheets("Schedule").UsedRange.Address, "$")(4)
n = Right(Worksheets("Schedule").Cells(i, 8).Value, 4)
If IsNumeric(n) Then n = CLng(n)
Worksheets("Schedule").Cells(i, 1).Value = _
Application.WorksheetFunction.VLookup(n, Worksheets("Codes").Range("A:B"), 2, 2)
Next i
End Sub
second code:
VBA Code:
Private Sub VLOOK_UP2()
Dim i As Long, n As Variant
For i = 1 To Split(Worksheets("Schedule").UsedRange.Address, "$")(4)
n = Right(Worksheets("Schedule").Cells(i, 9).Value, 4)
If IsNumeric(n) Then n = CLng(n)
Worksheets("Schedule").Cells(i, 1).Value = _
Application.WorksheetFunction.VLookup(n, Worksheets("Codes").Range("A:B"), 2, 2)
Next i
End Sub