-
May 8th, 2013, 01:34 PM
#1
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.
-
May 8th, 2013, 01:40 PM
#2
Re: VBA Excel 2007 Application.WorksheetFunction.Match help...
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
-
May 8th, 2013, 01:43 PM
#3
Re: VBA Excel 2007 Application.WorksheetFunction.Match help...
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
-
May 8th, 2013, 09:36 PM
#4
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)
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|