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?

 

[dpaton05]

My manager now wants to be able to enter the information just once and have it update the entire document. So, in my image, I only want D1:H1 to appear once, probably on the first sheet in the document but after entry of the purchase order and request numbers, he wants the entire document to be updated. This is instead of having the cells D1:H1 on every sheet.

• The sheets that will need updating are named by month and there are sheets for all 12 months of the year.
• Obviously, the format will be the same for every sheet
• I think that all you need is a loop but I am not sure of the vba

Thanks

Each monthly sheet looks like this

Book1
	A	B	C	D	E	F	G	H
1				Enter Req # and PO # in F1 and H1 and the spreadsheet will be auto populated	Req #		Purchase order #
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST
4
5
6
7
8
9
10
11
12
13
14
15
July

 

[Yongle]

I hope this does what you want
Feature added to allow user to clear all enties against a Req by entering any non-numeric character into H1

Delete the previous procedure and place the code below in the module of the sheet where the values are being entered in F1 and H1

Amend the sheet names in this line (to reflect those in the workbook) if I have guessed incorrectly
Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim Req As Range, PO As Range, Cel As Range, ws As Worksheet
    Set Req = Range("F1")
    Set PO = Range("H1")
    If Not Intersect(Target, PO) Is Nothing Then
        If PO > 0 And Req > 0 Then
            If Not IsNumeric(PO) Then PO.ClearContents
            For Each ws In ThisWorkbook.Sheets
                Select Case WorksheetFunction.Proper(ws.Name)
                    Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
                        For Each Cel In ws.Range("C1", ws.Range("C" & Rows.Count).End(xlUp))
                            If Cel = Req Then Cel.Offset(, -1) = PO
                        Next Cel
                End Select
            Next ws
        End If
        PO.ClearContents
        Req.ClearContents
    End If
    Application.EnableEvents = True
End Sub

 

[dpaton05]

Thanks for that Yongle, but that doesn't seem to work. I will give you a little background.

• I am going to put this code in allocation workbooks (wb) and each wb is for a financial year. Each wb also has every month in the year as sheets.
• I have another spreadsheet that is used to generate quotes.
• Each quote is one line but there will be many quotes
• When the quotes are finalised, I press a button and the quotes get copied to the relevant wb, depending on the date of the quote.
  • For instance, if a quote has the date of 20/1/19 it gets copied to the 2018-2019 Financial year document.
  • Another quote may have the date of 5/9/19 so it will be copied to the 2019-2020 Financial year document.

I have included a link to a file that is one of these workbooks. I have added some sample data on the October and December sheets. On the July sheet, I have added an area to enter the request no and PO no.

2020 - 2021 Allocation Sheet.xlsm

drive.google.com

When I first tried your code, it worked for some entries that were already in the workbook but it wouldn't work for all the entries. It doesn't seem to work at all now and I have no idea how to debug it.

Thanks Yongle

 

[dpaton05]

In the above file, try and enter 10077 for the request no and any number for the purchase order no on the July sheet. See how it won't update the PO number next to the Req no on October or December sheet.

 

[dpaton05]

Could you help me with this please yongle?

 

[Yongle]

Before post#11 the VBA was working as required, but I expected a few bumps in the road when I read this line in post#11
My manager now wants to be able to enter the information just once and have it update the entire document

Why did that lead to problems ?

The decision to use VBA cramps your ability to alter the structure of your workbook at will - every such change demands a review of the code
VBA is not Excel
In Excel users can copy, move and delete cells and Excel automatically takes care of all re-referencing
VBA is not like that
When VBA is told to take a value from cell P1 it will always do that even if users have inserted 4 new columns and column P values are now in column T
Users can see that the values are in column T and Excel will have dealt automatically with the new columns
BUT - unless VBA is told otherwise - VBA does not automatically know that it should now be looking in cell T1 for the value it needs
Event macros are triggered by specific events
2 actions that may appear "similar" when doing something manually in Excel may "trigger" different events

MOVING FORWARD - the spec has changed from what you asked in post#1 and I suggest we make a clean start

SUGGESTION A
I throw one radical suggestion at you to make everything much simpler for everyone
- ditch the monthly sheets (unless you expect to generate close to a million rows in any one year!)
- add a column to indicate which month it is
- all the data in one sheet means that it can be combined in any way you want for reporting etc
- use filtering to provide your monthly sheet
- a dropdown combined with some basic VBA could render filtering as quick as clicking on the correct sheet
Go and convince your manager
- his goal is to change all PO# values in ONE hit and this is the simplest way to achieve that

OF course, I appreciate that may be several other ramifications that make my suggestion unpopular or unpalatable
eg When the quotes are finalised, I press a button and the quotes get copied to the relevant wb, depending on the date of the quote

So you may prefer ...
SUGGESTION 2 - which assumes monthly sheets are being retained

Instead of adding sheet1 lets amend the ORIGINAL code (which worked for you) to allow users to enter values in F1 & H1 in ANY monthly sheet AND update the entire document

Let me know which way you want to go so that I can continue to help you

I get the impression that a Req# is UNIQUE to one financial year - is that correct ?
- the resaon I am asking is that I want to confirm that everything demanded from the code only affects ONE annual workbook at a time

thanks
Yongle

 

[dpaton05]

Thanks for getting back to me Yongle. My supervisor wants to keep the allocation sheets how they are with a sheet for each month. I explained it like you said but he said there were other reasons that it needed to be kept the same.

The request number is not unique to just one financial year as quotes may span over more than 1 financial year.

I really appreciate this help
Dave

 

[Yongle]

Q1 Are we going with Suggestion 2 ?

The request number is not unique to just one financial year as quotes may span over more than 1 financial year.

Q2 What do you want the VBA to do
- update current year ONLY or update current year + next year
- if the next year is also being updated what is the NAME of the workbook (I assume every year has same name amended to reflect year)

 

[dpaton05]

Sorry to be confusing Yongle.

1. I am going with suggestion 2
2. I just need the vba to update the current document.

Thanks,
Dave

 

[dpaton05]

I found something else very interesting.

This is the spreadsheet I am looking at:

Book1
	A	B	C	D	E	F	G	H
1	July				Req #		Purchase order #
2
3	Date	Purchase order #	Req #	Child Name	Service	Requesting Organisation	Caseworker Name	Price ex. GST
4		9	10114
5		7	10119
July

You can see that there are 5 digists in the cells in column C. There is a small green triangle at the top left hand corner. If you drop down the list shown by the green arrow, it says "number stored as text" but there is also the option that says "convert to number".

You code works on all sheets in the workbook if there is a little green arrow next to the cell but if I click on convert to number, it won't update the PO number, eventhough the req number is the same.

This might mean that the code that copies the entries to the workbooks may need to be updated??