VBA Excel 2007 Application.WorksheetFunction.Match help...

Good day everyone,
I hope i'm in the right place for VB Excel questions.

I have difficulty finding an answer to my problem. I have a workbook set up for the type of job I’m doing. My codes are mostly mine own and help from others…including multiple website with excel help.
I have hit a wall with all the information passing through I am just lost when it comes to complex coding.

I have a sub Test that includes an MsgBox Application.WorksheetFunction.Match code.

I can’t upload my workbook as there is way too much information.

I have 7 sheets set up, one of them is for my normal control (Hidden sheet) and I have a “Archives” sheet that is also hidden for data and code purpose.

So 5 sheet with a flow, meaning that “The user” will start from sheet1 and work his way to sheet 2 and so on. It is possible to go on any 5 sheets. Depending on what the user is working on. But mainly, it goes from one sheet to the other.

The process is kind of too big to explain on here but I am new, so just wondering what kind of info will be required.
This is kind of what I’m looking for…

The workbook is based on ActiveCell.row method and also Application.WorksheetFunction.Match. Every time you will select a row or a cell and click on the command button “Complete” it will look up in sheet5 and match column A to find the exact same info from Sheet3.

I need to incorporate the following code in my sheet3

Code:

---

For Each Cell In ActiveCell
            If Cell.Value Like "*[!0-9]/[A-Z]" Then
                Cell.Value = Left(Cell.Value, Len(Cell.Value) - 2)
            End If
        Next Cell

---

So that each time I make a transfer it will delete the /A from my Evaluation but also look in my sheet5 then delete the /A then do the initial Transfer.

I would love to insert my workbook for explanation but unfortunately that will not be possible… Posting codes is not an issue.

This is actually the full code in my sheet3:

Code:

---


Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.EnableEvents = False

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 1)).Copy
Sheets("Contracts").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'This function will look for the requisition number in the Reports Sheet and...
RptProjRowNum = Application.WorksheetFunction.Match( _
ActiveSheet.Range("A" & ActiveCell.Row).Value, _
Worksheets("Report").Range("A5:A10000"), 0) + 4

Worksheets("Report").Range("J" & RptProjRowNum).Value = _
Range("E" & ActiveCell.Row).Value

'This function copies the Contract Reviewer info to the Report Sheet on the same row
Worksheets("Report").Range("K" & RptProjRowNum).Value = _
Range("D" & ActiveCell.Row).Value
'and
'This function will replace the "Comments" on the same row
Worksheets("Report").Range("V" & RptProjRowNum).Value = _
Range("C" & ActiveCell.Row).Value

'This clears the content in the selected row from A to F and Sort row 5 to 30

    Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 5)).ClearContents
    Rows("5:30").Select
    ActiveWorkbook.Worksheets("Evaluation").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Evaluation").Sort.SortFields.Add Key:=Range( _
        "A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Evaluation").Sort
        .SetRange Range("A5:E30")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Unload Me
    Range("A5").Select
    Sheets("Contracts").Select
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub

---

Sorry forgot my top page info:

Code:

---

Dim NewRwHt As Single
Dim cWdth As Single, MrgeWdth As Single
Dim C As Range, cc As Range
Dim ma As Range
Dim TCol As Long
Dim TRow As Long
Dim RptProjRowNum As Long

Sub Test()
    MsgBox Application.WorksheetFunction.Match(Range("A5:A30"), Worksheets("Report").Range("A5:A10000"))
    MsgBox Application.WorksheetFunction.Match(Range("A5:A30"), Worksheets("Archives").Range("A1:A10000"))
End Sub

---

Hide, then UNLOCK, then use the info. Lock, un-hide, and it should work. (Could try recording a macro to see how it works)