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]

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

Place code in a standard module and run
- new sheet added
- type of value in each cell (in Req# column) in each sheet listed
- data filter enabled to simplify filtering by sheet and type of value

The code is a bit lazy - it assumes that if value not text then it's a number (but should be OK for this job)

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

 

[dpaton05]

I put the code in that you included in post 30 and it worked great, what this code for?

 

[dpaton05]

I just worked it out. Do I need it as it seems to work fine without it?

 

[Yongle]

I fixed the code in post#30 to work with the "bad" inconsistent format in Req# column

Some of your data was not consistent and that was why you had the problem with the code only working for some values
It is better practice if the format of values within one column is consistent - otherwise it may trip you up again in the future

It is your decision whether you want to change the values to make them consistent
- the code in post#31 is a tool to help you with that task

To fix the problem - manually select all 12 sheets together, select column C and format as text, then ungroup the sheets

 

[dpaton05]

Where do I put your code in post 31?

 

[Yongle]

Where do I put your code in post 31?

please read the first line of that post

 

[dpaton05]

Sorry about that.

I have multiple allocation sheets, do I need to put it in a standard module of each allocation sheet and run it?

 

[dpaton05]

So I think I can read the code and it looks like it updates column C to the text format, is that correct?

 

[Michael M]

So I think I can read the code and it looks like it updates column C to the text format, is that correct?

Yes, updates column "C"
AND
it goes in a standard MODULE, not in each sheet, as it has an array that modifies each sheet from that module

 

[dpaton05]

Do you mean the code in post 31 goes in a standard module?