CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4
  1. #1
    Join Date
    May 2013

    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

    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.

  2. #2
    Join Date
    May 2013

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

    This is actually the full code in my sheet3:

    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
    '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
        ActiveWorkbook.Worksheets("Evaluation").Sort.SortFields.Add Key:=Range( _
            "A5"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        With ActiveWorkbook.Worksheets("Evaluation").Sort
            .SetRange Range("A5:E30")
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
        End With
    Unload Me
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    End Sub

  3. #3
    Join Date
    May 2013

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

    Sorry forgot my top page info:

    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

  4. #4
    Join Date
    Jan 2006
    Fox Lake, IL

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

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

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Windows Mobile Development Center

Click Here to Expand Forum to Full Width

On-Demand Webinars (sponsored)

We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.