find matching cell and goto (1 Viewer)

sln8458

New member
Local time
Today, 10:05
Joined
Aug 4, 2015
Messages
3
Hi all,

I have a workbook with multiple worksheets, the workbook is used for project cost monitoring with a new worksheet for each project. The sheets have unique names associated with their project.

Col's A-M are currently used.

Col A contains the Purchase Requisition No.
Col D contains the Purchase Order No.

There is a worksheet (named report) that is linked to the purchasing system via an external workbook. From this I have a number of columns auto filling via Vlookup from the 'report' tab,

eg: =VLOOKUP(B7,report!$A$1:$V$64000,8,FALSE)
(note, B7 is concatenate of A7 and ' , due to an issue with the report I have no control over)

A single PR does not guarantee a single PO:banghead: so I have used conditional formatting to highlight where there are multiple PR entries in the report sheet.

From here I have used a array to list all PO associated with a single PR
eg: '{=IFERROR(INDEX(report!$H$8:$H$64000,SMALL(IF($C$18=report!$A:$A,ROW(report!$H$8:$H$64000)-14,""),ROW()-16)),"Error")}

The above array is copied into 25 cells, and works fine :) with me just entering the PR number highlighted by the previous conditional formatting.

Here is where I'm stuck:confused:

I would like to use the value entered into C18 to search Col A for the same value.

At which point I will add a new row below the existing entry, copy cells down with formulae for all columns & then double back to the PO column to manually enter the one (or more) additional PO no.( the last bit I have completed previously)

I hope that makes sense?

SteveN
 

sln8458

New member
Local time
Today, 10:05
Joined
Aug 4, 2015
Messages
3
Well I've managed to resolve my problem:)

I named cell C18 as target_pr.
Then using the code below I found the matching value in col A.

Sub Duplicate_PR()
Application.ScreenUpdating = False
'find original PR number

For Row = 170 To 1 Step -1
If Range("A" & Row).Value = Range("target_pr").Value Then
Range("A" & Row).Select
Exit Sub
End If
Next

End Sub
This finds the location just fine.
Then to add a new row at that location:

Sub InsertCopyRow()
ActiveCell.Offset(1, 0).EntireRow.Insert
ActiveCell.EntireRow.Copy ActiveCell.Offset(1, 0).EntireRow
'ActiveCell.EntireRow.ClearContents
'ActiveCell(x + 2, 1).Copy
'ActiveCell(x + 1, 1).PasteSpecial xlPasteFormulas
'ActiveCell(x + 1, 1).ClearContents

'''SLN PO
'copy the formats to the new row
ActiveCell(x + 2, 4).Copy
ActiveCell(x + 1, 4).PasteSpecial xlPasteFormulas
ActiveCell(x + 1, 4).Borders.LineStyle = xlContinuous
ActiveCell(x + 1, 4).ClearContents
End Sub
This duplicates the row and leaves the active cell as the one which requires a new PO number inserting.

Now in doing this I discovered an issue with my array formulae above.

'{=IFERROR(INDEX(report!$H$8:$H$64000,SMALL(IF($C$18=report!$A:$A,ROW(report!$H$8:$H$64000)-14,""),ROW()-16)),"Error")}

When I insert a new row the table array I loose the first line & hence the data, not helpful.

Solution:

Place =row() in any cell on row 16, then I named the cell row_increment
this displays the row number in the cell
thus when a new row is added this increments inline with the row number.

Then replace
,""),ROW()-16)),"Error"

with

,""),ROW()-row_increment)),"Error"

and now when I insert my new row the table remains intact with all original data.

Now this may appear 'messy' to seasoned professionals who frequent this forum. but it works ................ at the moment :)

Hope this helps others as I hate leaving a request for help hanging;)

SteveN
PS
Forgot to say, all named cells are 'worksheet' names not 'workbook' this allows me to have a 'template' worksheet to be copied for future projects with all code intact.
 

Users who are viewing this thread

Top Bottom