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]

I think I also need to format column B as text as I just realised that some cells in column B are formatted as a date.

 

[dpaton05]

I had this feature working but it has stopped working and I don't know why. I have slightly altered it and I thought I could just update the code but the original code has stopped working.

Before I tried updating it, there were 2 cells on a sheet called July for entry of a purchase order number and a request number. The request number went in F1 and the purchase order number went in H1. There is a sheet for every month in the year and they are all identical except the 2 cells were only on the july sheet. The heading is in row 3 with data started in row 4. The request number is entered in column C and the purchase order number is entered in column B.

The request number will be known first and that is entered in column C for the required jobs. I used to have a feature that worked where I would enter the request number in F1 and when the purchase order number was received, it could be entered in H1 of the July sheet and it would find every instance of the request number in column C for each monthly sheet of the document and update it so the purchase order number is entered in column B for that job.

I don't really understand this code that someone helped me with so I couldn't find the problem.

This is the code in the July sheet, where the cells to enter the request number and purchase order number were.
I say were as I have updated it so the cells to enter the request and purchase order numbers are now on a sheet called Totals. The request number now goes in I25 and the purchase order number goes in K25.

Option Explicit
'this is triggered whenever cell H1 is amended in any of the listed sheets
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim Req As Range, PO As Range
    Select Case WorksheetFunction.Proper(sh.Name)
        Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
            Set Req = sh.Range("F1")
            Set PO = sh.Range("H1")
            If Not Intersect(Target, PO) Is Nothing Then
                Application.EnableEvents = False
                If PO <> "" And Req <> "" Then Call UpdateEverySheet(Req, PO)
                PO.ClearContents
                Req.ClearContents
                Application.EnableEvents = True
            End If
    End Select
End Sub
'this is called by Sheet_Change and loops through all monthly sheets creating required entries
Private Sub UpdateEverySheet(Req As Range, PO As Range)
    Dim sh, ws As Worksheet, Cel As Range, ReqRng As Range
    If UCase(PO) = "X" Then PO = ""
    For Each sh In Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
        Set ws = Sheets(sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then Cel.Offset(, -1) = PO
        Next Cel
    Next sh
End Sub

Every monthly sheet except July had this code but I don't think it is needed.

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

Could someone help me work out why the code is not working and to update it so I can test it in a live environment please?

 

[dpaton05]

I had forgot I started this thread so I started a new thread but I just found this thread after I had already made the new one.

I don't know what has happened but now if I enter a request number and a purchase order number in the july sheet, nothing will be updated and the numbers also will not be deleted as they used to be.

This is the code in module1, none of the other sheets or modules have code in them.
I have updated the workbook so the cells to enter the request and purchase order numbers are now on a sheet called Totals. The request number now goes in I25 and the purchase order number goes in K25.

Option Explicit
Sub IsItANumber()
    Dim sh, ws As Worksheet, temp As Worksheet, ReqRng As Range, Cel As Range, CellContent As Variant, a As String
    Set temp = Sheets.Add(before:=Sheets(1))
    temp.Range("A1:C1") = Array("Sheet", "Cell", "Type")
    For Each sh In Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
        Set ws = Sheets(sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
            For Each Cel In ReqRng
                CellContent = Cel.Value
                If VarType(CellContent) = 8 Then a = "text" Else a = "number"
               
                temp.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(, 3) = Array(ws.Name, Cel.Address(0, 0), a)
            Next Cel
    Next
    temp.Range("A1").AutoFilter
End Sub



'this is triggered whenever cell H1 is amended in any of the listed sheets
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)
    Dim Req As Range, PO As Range
    Select Case WorksheetFunction.Proper(sh.Name)
        Case "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"
            Set Req = sh.Range("F1")
            Set PO = sh.Range("H1")
            If Not Intersect(Target, PO) Is Nothing Then
                Application.EnableEvents = False
                If PO <> "" And Req <> "" Then Call UpdateEverySheet(Req, PO)
                PO.ClearContents
                Req.ClearContents
                Application.EnableEvents = True
            End If
    End Select
End Sub
'this is called by Sheet_Change and loops through all monthly sheets creating required entries
Private Sub UpdateEverySheet(Req As Range, PO As Range)
    Dim sh, ws As Worksheet, Cel As Range, ReqRng As Range
    If UCase(PO) = "X" Then PO = ""
    For Each sh In Array("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
        Set ws = Sheets(sh)
        Set ReqRng = ws.Range("C4", ws.Range("C" & Rows.Count).End(xlUp))
        For Each Cel In ReqRng
            If Val(Cel) = Val(Req) Then Cel.Offset(, -1) = PO
        Next Cel
    Next sh
End Sub

Thanks guys

 

[Peter_SSs]

I had forgot I started this thread so I started a new thread but I just found this thread after I had already made the new one.

.. so I have merged the two threads