Hi,
I have a VBA code set up to import and copy excel files onto a master sheet.
The VBA code also checks if the value in the "C" column (Text3) contains a specific location, and if it does the "B" column (Text2) changes accordingly.
It currently works fine with the code below, however, there are hundreds of possible locations, and the VBA code would have to contain IF functions separately for each one.
I've tried playing around with some INDEX/MATCH VBA code, but to no success. A table of the corresponding Locations and respective codes can be used on a separate sheet.
Any advice to reducing the code?
Thanks in advance!
I have a VBA code set up to import and copy excel files onto a master sheet.
The VBA code also checks if the value in the "C" column (Text3) contains a specific location, and if it does the "B" column (Text2) changes accordingly.
It currently works fine with the code below, however, there are hundreds of possible locations, and the VBA code would have to contain IF functions separately for each one.
I've tried playing around with some INDEX/MATCH VBA code, but to no success. A table of the corresponding Locations and respective codes can be used on a separate sheet.
Any advice to reducing the code?
Thanks in advance!
VBA Code:
With wb.Sheets(1)
Text1 = .Cells(1, 1).Value
Text2 = .Cells(1, 2).Value
Text3 = .Cells(1, 3).Value
Text3a = UCase(Mid(Text3, InStr(Text3, " ") + 1, 5)) '5 characters following the first <Space> in the field
End With
ThisWorkbook.Activate
If "LOCATION1" = Text3a Then
Text2 = "123"
Else
End If
If "LOCATION2" = Text3a Then
Text2 = "456"
Else
End If
If "LOCATION3" = Text3a Then
Text2 = "789"
Else
End If
.Cells(NewTempRow, 1) = Text1
.Cells(NewTempRow, 2) = Text2
.Cells(NewTempRow, 3) = Text3
NewTempRow = NewTempRow + 1
End Sub