Automatically look up a table in another sheet and change cell values accordingly?

[Jockdk]

Hi Guys,

I'm fairly new to VBA so I was wondering if anyone could please help me with an issue?

I basically have a macro that copies data from one sheet, transposes it and then convets the cell data into another value.

I can do this in a static way by manually typing in every variable like this......


Sub ChangeValuesInTable()
' This will change the values to match the correct ones in the new sheet
Dim my1check As Boolean
endrange = Range("A65000").End(xlUp).Row
For i = 4 To endrange

'Column D Only
Column = "D"
'Leave
my1check = Range(Column & i).Value Like "1*"
If my1check = True Then
Range(Column & i).Value = "Leave"
End If
....
Etc Etc Etc
....
my5check = Range(Column & i).Value Like "C/O*"
If my5check = True Then
Range(Column & i).Value = "A 09.00-17.00"
Range(Column & i + 1).Value = "A 17.00-09.00"
End If

Next i

This works a treat for one user. The problem is that I have to type in every variable for every column up to AH. This takes ages and is not dynamic.

Rather than type every variable manually I'd like to get Excel to look up a table/sheet where all of these variables are set by the user. This is because they will have to be variable depending upon the user.
e.g "1"= "Leave" for one user but another guy using the macro might want "L"= "Leave".

I have tried various ways but I'm getting the syntax wrong or I shouldn't be using the "If" function or maybe I should be using something else?

Any help is really appreciated guys.

Cheers
Dave