Entering a set number whenever a criteria is met

[dpaton05]

I have a spreadsheet that has many quotes in it.

• Each line is a quote and the quotes all have a request number.
• The quotes start in row 4 and go down from there with heading/title etc above
• Column C has the request number
• I need a way to be able to search for all the request numbers and enter a purchase order number for that quote
• The purchase order number is in column B

At the top of the spreadsheet, I have 2 cells, to enter the purchase order number aND one to enter the request number.

• The cell to enter the request number is in F1 and the cell to enter the purchase order number is in H1.

How would I go about entering a request number in F1 and a purchase order number in H1 and I need the purchase order number to be copied to every quote with that request number, as I am not sure?

 

[Yongle]

Add-in XL2BB can be downloaded to allow you to post sample data and required results
Click on link above reply window and read all the instructions

 

[dpaton05]

Thanks, that tool is pretty cool. Here is the capture:

Book1
	A	B	C	D	E	F	G	H	I	J
1	501 NPSS December				Req #		Purchase order #
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST	GST	Price inc. GST
December

The quotes will start in row 4.

 

[dpaton05]

I don't know if the idea I am suggesting is the best way to do this. There may be better ways but it was all I could think of.

 

[dpaton05]

Book1
	A	B	C	D	E	F	G	H
1					Req #	10119	Purchase order #	5000
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST
4	13/12/2019		10110	xxxxxxxx	xxxx
5	13/12/2019		10099	xxxxxxxx	xxxx
6	14/12/2019		10099	xxxxxxxx	xxxx
7	16/12/2019		10119	xxxxxxxx	xxxx
8	16/12/2019		10130	xxxxxxxx	xxxx
9	18/12/2019		10099	xxxxxxxx	xxxx
10	20/12/2019		10130	xxxxxxxx	xxxx
11	20/12/2019		10132	xxxxxxxx	xxxx
12	21/12/2019		10119	xxxxxxxx	xxxx
13	21/12/2019		10130	xxxxxxxx	xxxx
14	21/12/2019		10133	xxxxxxxx	xxxx
November

Here is an example with data. Using the example, I want to enter 5000 in the purchase order column (B) for every row that has a request number (C) of 10119. As the purchase order number for a given request is received, it will be added into H1 and for every quote that has the request number entered in F1, the purchase order number will need to be entered in column B.

I know this would be simply done with a bit of vba. Could someone help me please?

 

[Yongle]

Try this
The code is trigerred when F1 already contains a value and a value is entered in H1

right click on sheet tab \ click on View Code \ paste the code into the window that opens \ back to Excel with {ALT}{F11}

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Req As Range, PO As Range, ReqRng As Range, Cel As Range
    Set Req = Range("F1")
    Set PO = Range("H1")
    Set ReqRng = Range("C4", Range("C" & Rows.Count).End(xlUp))

    If Not Intersect(Target, PO) Is Nothing Then
        Application.EnableEvents = False
        If PO > 0 And Req > 0 Then
            For Each Cel In ReqRng
                If Cel = Req Then Cel.Offset(, -1) = PO
            Next
        End If
        PO.ClearContents
        Req.ClearContents
        Application.EnableEvents = True
    End If
End Sub

I will be offline for the next 12 hours and will deal with any observations & queries after that

 

[dpaton05]

Nothing happens when I add that code and try to update H1 and F1 already has a value.

 

[Yongle]

The code works - I tested it

Are you sure you put it in the correct place?
It will only work if placed in the sheet module

 

[dpaton05]

I had the code at the bottom of the module. I moved to to the top and now it works perfectly. Thank you

 

[Yongle]

I had the code at the bottom of the module. I moved to to the top and now it works perfectly

That is a new one! I wish I understood what you did that VBA did not approve of.

I am glad it now works