WorksheetFunction.Match returns error with String variable, but not with number

[IntisarN]

Here is the current code that I've written so far:

Sub Create_T_Tables()

Application.ScreenUpdating = False
Application.CutCopyMode = False

Dim RowCount As Long, N_Row As Long, N_Table_Sub As Long, N_Column As Long, N_Table As Long, I_Table As Long, S_Table As Long, C As Long, C2 As Long
Dim T_List As String, FilePath As String, S_Name As String, EP As String, Gender As String
Dim WS As Worksheet, T_List_WS As Worksheet, S_WS As Worksheet, T_Table_WS As Worksheet
Dim MainWB As Workbook, PasteWB As Workbook

T_List = "Termination_Tables_List"
Set MainWB = ActiveWorkbook

'For Each WS In MainWB.Worksheets
' WS.Visible = xlSheetVisible
'Next

Set T_List_WS = MainWB.Sheets(T_List)
Set T_Table_WS = MainWB.Sheets("Temp_Table")
FilePath = MainWB.Path & "\AXIS_Import.xlsx"

N_Row = 177
N_Table_Sub = 4
N_Column = 121
RowCount = T_List_WS.Cells(Rows.Count, "A").End(xlUp).Row
N_Table = RowCount - 1

For I_Table = 1 To N_Table
EP = Range("EliminationPeriod").Offset(I_Table, 0)
Gender = Range("Gender").Offset(I_Table, 0)
For S_Table = 1 To 5
If EP = 3 Then
If S_Table = 2 Then
S_Table = 3
End If
Else
If S_Table = 1 Then
S_Table = 2
End If
End If
S_Name = Range("SourceTables").Offset(I_Table, S_Table - 1)
If S_Name <> "" Then
For Each WS In MainWB.Sheets
If Right(WS.Name, Len(WS.Name) - 6) = S_Name Then
WS.Activate
If Gender = "M" Then
Range("A1").Select
Range(Selection.End(xlDown), Selection.End(xlToRight)).Select
Selection.Copy

T_Table_WS.Activate
Cells(4, WorksheetFunction.Match(EP, Range("2:2"), 0)).Select
C = 1
Do Until Selection.Offset(C, 0) = ""
C = C + 1
Loop
Cells(4 + C, WorksheetFunction.Match(EP, Range("2:2"), 0)).Select
Selection.PasteSpecial Paste:=xlPasteValues, operation:=xlNone, skipBlanks:=False, Transpose:=True

Exit For
End If
End If
Next
End If
Next S_Table
Next I_Table


More specifically this is the part which returns an error:

Cells(4, WorksheetFunction.Match(EP, Range("2:2"), 0)).Select

However, when I use

Cells(4, WorksheetFunction.Match(3, Range("2:2"), 0)).Select or
Cells(4, WorksheetFunction.Match(6, Range("2:2"), 0)).Select,

since EP can only have 2 values, '3' or '6' it works fine. If I put EP in the function it gives me error 1004: Unable to get the match property of the Worksheet Function class.

This is really annoying, it could be something really simple that I'm missing. But I'm developing this code further and I want to use EP instead of the exact values. Any help will be appreciated.

Thanks,

[Norie]

Have you tried declaring EP as Long instead of String?

PS Can you add code tags when posting code?

[IntisarN]

Yep EP As Long worked.

I wasn't aware of the code tags - will do in future.

Thank you for the quick response!