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 found that if the req numbers are entered in manually, they have a little green arrow but if they are copied to the workbook at a result of my copy code, they do not have any little green arrow.

 

[Michael M]

So why not insert a line in your copy code to make sure column "C" is formatted as Text not numbers ???

 

[dpaton05]

Is this the right code to format as text?

.Range("C:C").NumberFormat "@"

 

[dpaton05]

My code before this addition works fine so I just thought I would add a line in the middle to make sure column C was text

I added this line Columns("C:C").NumberFormat "@"

        With wsDst
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range.Resize(, 7).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                'This copies the first 7 columns, i.e. A:G, of the current row of the table to column A in the destination sheet.
                tblrow.Range(, 10).Copy
                'This pastes in the figures in the first 7 columns starting in column A
                .Range("H" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteFormulasAndNumberFormats
                'Formats column C as text
                Columns("C:C").NumberFormat "@"
                'Overwrites the numbers pasted to column I with a formula
                .Range("I" & Rows.Count).End(xlUp).Offset(1).Formula = "=IF(RC[-4]=""Activities"",0,RC[-1]*0.1)"
                'Overwrites the numbers pasted to column L with a formula
                .Range("J" & Rows.Count).End(xlUp).Offset(1).Formula = "=RC[-1]+RC[-2]"
                'Adds currency formatting to total ex gst column
                .Columns(8).NumberFormat = "$#,##0.00"
                'Adds Australian date format to date column
                '.Range("A:A").NumberFormat = "dd/mm/yyyy"

Only problem is that when I try and run it, I get the error message of NumberFormat method of range class failed.

 

[Michael M]

either thator

 .Columns("C:C").NumberFormat = "@"

 

[dpaton05]

Earlier in the procedure, I have

Set wsDst = Workbooks(DocYearName).Worksheets(Combo)

then I have

        With wsDst
                'Formats column C as text
                .Columns("C:C").NumberFormat "@"

When I run the code, I still get the same error.

 

[Michael M]

why not put the lin at the start of the code provided by @Yongle ??

 

[Yongle]

I am glad that you have determined why some values did not match up
- some numbers are numbers, other numbers are text (not an uncommon issue!)
Later, I will provide you with some basic code to scan monthly sheets to identify those "rogue" entries

Personally I do not like numbers being formatted as text simply to make them look the same
- it is too easy to end up with a mixed bag of text and numbers (your current issue)
- Excel wants numbers to be numbers and is very stubborn about it
- treating numbers as text can be a similar experience to playing Whack-a-Mole

As a general rule it is easier to work with Excel and allow numbers to be numbers and simply display them to a consistent number of digits

 

[Yongle]

Sorry to be confusing Yongle.

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

It is not a problem
I will revise the original code accordingly

 

[Yongle]

Here is the revised code based on SUGGESTION 2
- test on a COPY of your workbook

Numbers and numbers treated as text are both found (by using the VAL function in the comparison)
- hopefully that should eliminate the issue with those "rogue" entries

x (in H1) removes all PO numbers against specified Req#
(the x can be upper or lower case)
To use a different letter amend this line
If UCase(PO) = "X" Then PO = ""

Delete the previous code - make sure that all WorkSheet_Change procedures have been removed - to prevent possible conflict with the latest code

The code below code must be placed in ThisWorkbook code window - it will NOT work if placed anywhere else

Let me know how you get on
- it is virrtually identical to the original code but adapted to be triggered on any of the 12 monthly sheets and to update all those sheets

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