• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Offset from an application.WorksheetFunction.Match

Excelnoub

Excelnoub

Member
Good day everyone,

this is a little complicated. I have a workbook that has a find and match code. Works well and like a charm. Problem is with the following code:

[pre]
Code: 
retenderletter = InputBox("What is the re-tender letter", "Re-Tender")

If retenderletter <> "" Then

'''''''''''''''''''''''''''''''''''''''''''''''
'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

'This function will replace the "Comments" on the same row
Worksheets("Report").Range("V" & RptProjRowNum).Value = _
Range("C" & ActiveCell.Row).Value

'This will copy the requisition number from the selected row and send it back in the MERX sheet
Cells(ActiveCell.Row, 1).Value = Cells(ActiveCell.Row, 1).Value & "/" & retenderletter
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 2)).Copy
Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

‘I need also to do this to my Report Sheet
Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
[/pre]

I need to search for the for the RptProjRowNum and offset the rows information, so an ActiveCell.Offset(1, 0).EntireRow.Insert But in my Column A add the ActiveCell.Row from the main sheet to add the Cells(ActiveCell.Row, 1).Value & "/" & retenderletter in my Report sheet


Let’s say that the ActiveCell.row, 1 = Test and the retenderletter = C (Sheet "Evaluation")

So it will change the Test to Test/C when I transfer the info to my MERX sheet.

On my Report Sheet I have Info until column 24, I need to offset the new row from column A to X but to add the "/" & retenderletter to my + 1 row to Column A

So that now my Report sheet will be


Test

Test/C


So hard to explain when you have the image in head but not in words
 
I had a little trouble understanding everything you described, but can you do something like this?

[pre]
Code: 
MyVariable = Cells(ActiveCell.Row,"A").Value
Cells(ActiveCell.Row,"X").Value = MyVariable & "/" & retenderletter
[/pre]
 
It actually does nothing...


The following works but missing information:


Code: 
Cells(ActiveCell.Row, 1).Value = Cells(ActiveCell.Row, 1).Value & "/" & retenderletter

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 2)).Copy

Sheets("MERX").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

Sheets("Report").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

[Code/]


This will transfer the info to my MERX sheet and then also transfer the information to my Report Sheet. Problem is:


In my report sheet there is information on that matching row from 1(A) to 24(X).


The first code I have with the Application.WorksheetFunction.Match is the following in a Sub function: 


[Code]

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

[Code/]


So from my Sheet1 going to my Sheet4 everything matches column A in my Report sheet.


But one of my dilemma is the one has to create itself based on the new function. Meaning that, in my Sheet3 on in my column A (let’s say A5) will have Test therefore in my Report there has to be a Test in Column A (Somewhere doesn’t matter the row. 


If I do a normal transfer: 


[Code]


RptProjRowNum = Application.WorksheetFunction.Match( _

ActiveSheet.Range("A" & ActiveCell.Row).Value, _

Worksheets("Report").Range("A5:A10000"), 0) + 4


'add the "Date Posted", "Closing Date" and "Bid Validity" on the same row

Worksheets("Report").Range("H" & RptProjRowNum & ":J" & RptProjRowNum).Value = _

Range("C" & ActiveCell.Row & ":" & "E" & ActiveCell.Row).Value


'This function will replace the "Comment" on the same row

Worksheets("Report").Range("V" & RptProjRowNum).Value = _

Range("F" & ActiveCell.Row).Value

[Code/]


It will transfer the information from my Sheet2 (MERX) to my sheet5 (Report) matching column A and will insert the Activecell.row F to my V Activecell.row in my Sheet5.


But my sheet3 I need to make some adjustment. By creating a new row based on my transferred row information it has to first create a new row on my sheet5 on that Activecell.row and offset the hole information on this new line but at Column A it needs to add the /C (Example) to the new row column A. 


Now Test/C will be created in my MERX sheet(2) and will also be created in my Report sheet(5) therefore this will be able to do the Match application process.
 
It may just be too late in the day for me, but I'm still getting lost. This may just be one of the times when uploading a workbook would be best to help us keep track of where everything is, and where you are wanting it to go. Sorry.
 
Hi, Excelnoub!

I met Luke M at the destination of our trip to how-to-get-lost-and-don't-understand-anything. Please upload a sample file, or at least paste involved worksheet ranges before and after processing, indicating what are the steps.

Regards!


@Luke M

Hi!

For me is still early even entering into this topic looks like if walking at midnight thru a forest.

Regards!
 
Here is the completed code :D

[pre]
Code: 
Set rngReqNo = ActiveCell
strReqNo = rngReqNo.Value

If strReqNo Like "*/[A-Z]" Then strReqNo = Left(strReqNo, Len(strReqNo) - 2)
Set rngFoundReqNo = Sheets("Report").Range("A:A").Find(What:=strReqNo & "*", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not rngFoundReqNo Is Nothing Then
Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 1)).Copy
Sheets("Contracts").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

'  Copy the Contract Reviewer info to the Report Sheet on the same row
rngFoundReqNo.Offset(, 10).Value = rngReqNo.Offset(, 3).Value 'D to K
'Replace the "Comments" on the same row
rngFoundReqNo.Offset(, 21).Value = rngReqNo.Offset(, 2).Value 'C to V

With Sheets("Report").Range("A:A").Find(What:=strReqNo & "*", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False)
rngFoundReqNo.Resize(, 24).Copy Destination:=.Cells
.Value = strReqNo
End With

With Sheets("Contracts").Range("A:A").Find(What:=strReqNo & "*", _
LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, _
MatchCase:=False)
rngFoundReqNo.Resize(, 1).Copy Destination:=.Cells
.Value = strReqNo
End With

Range(Cells(ActiveCell.Row, 1), Cells(ActiveCell.Row, 5)).ClearContents

Else
'No Match Found
MsgBox "Cannot find " & strReqNo & " on 'Report' sheet. ", _
vbExclamation, "Requisition Number Not Found"
Unload Me
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

End If
[/pre]
 
You must log in or register to reply here.
Back
Top