If number in culumn 'A' match set number another number in column 'I'

A series of numbers are inserted into the cells in column 'A' - if say cell 'A1' number matches a number (in the code) a different number is inserted into the cell in column 'I'.......

ie
When any cell in column A containes 4505 the number 194069 is placed into column I
When any cell in column A containes 4513 the number 194079 is placed into column I
When any cell in column A containes 4521 the number 194089 is placed into column I
When any cell in column A containes 4548 the number 194099 is placed into column I

and then once finished the data in column A is cleared

Hi MAButler

How many numbers we talkin' 'bout here? Less than 10? More that 50? Somewhere in between?

Hi MAButler
You could try this sheet change code

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Select Case Target.Cells.Value
        Case "4505"
            Target.Offset(0, 8).Value = "194069"
        Case "4513"
            Target.Offset(0, 8).Value = "194079"
        Case "4521"
            Target.Offset(0, 8).Value = "194089"
        Case "4548"
            Target.Offset(0, 8).Value = "194099"
        End Select
    End If
End Sub

Let me know of issues.

Hi MAButler
You are correct

just add another case line with ""

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 Then
        Select Case Target.Cells.Value
        Case "4505"
            Target.Offset(0, 8).Value = "194069"
        Case "4513"
            Target.Offset(0, 8).Value = "194079"
        Case "4521"
            Target.Offset(0, 8).Value = "194089"
        Case "4548"
            Target.Offset(0, 8).Value = "194099"
        Case Else
            Target.Offset(0, 8).Value = ""
        End Select
    End If
End Sub

I don't understand this

extract data from the input sheet - so when the data gets to the new sheet its there.........

If the user has input the requisite numbers in column A of this sheet, column I of this sheet will be populated accordingly. When you extract data from this sheet to another sheet, the information will be there in the new sheet. I sense I'm missing something...what is it? What's the process?

Hi MAButler

I've looked at your most recent workbook attachment and have some questions.

Firstly
I'd appreciate it if you'd describe your process to me so I'm not guessing which is the chicken and which is the egg. I'm happy to help but unless I understand the steps, it's difficult for me to envision what you want and/or where you want it. Having said that, these are my observation based on my review of your file:

Secondly
It APPEARS that Sheet Info is a data entry sheet.
If this is true, then it would appear that, if the user entered 4505 in Sheet Info cell C5, you want Sheet Info Cell C6 populated with 194069, etc, etc.
If this is true, this code, inserted into Sheet Info will accomplish that

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C5:H5")) Is Nothing Then
        Application.EnableEvents = False
        Select Case Target.Cells.Value
        Case 4505
            Target.Offset(1, 0).Value = "194069"
        Case 4513
            Target.Offset(1, 0).Value = "194079"
        Case "4521"
            Target.Offset(1, 0).Value = "194089"
        Case "4548"
            Target.Offset(1, 0).Value = "194099"
        Case ""
            Target.Offset(1, 0).Value = 0
        Case Else
            Target.Offset(1, 0).Value = 0
        End Select
        Application.EnableEvents = True
    End If
End Sub

Thirdly
It APPEARS that Sheet Data is a repository for all data from Sheet Info.
If this is true, and Sheet Info IS a data entry sheet, how and where in the process does data get passed from Sheet Info to Sheet Data?

When, where and how would the user do this

If the inputter makes an error and then has to delete "4505", is there a code to delete the content in the cells on the same row Column H and I.

Do you HAVE a form and code in place for the user to make corrections?

Fourthly
I can't answer this question

if i did not use the code on the input sheet could it be joined into the code i have been working on to extract data

'cause I don't know your process and I don't know what your extract code is.

MAButler...layout your process...help me understand it...perhaps I can help with solutions. At the moment, I'm in a fog.

Ok……. Sorry I have been working on this for a while so this would be a good exercise, fresh eyes and all that.

This is how I would like it to work, some parts are already working and well other parts are I would like.

A staff member will then type data straight on to the Sheet1 (Data), the first thing they type is the account number (the cell will only accept one of the four listed on Sheet2 (Info)), as soon as they type this number: - the reference , date as in today (but needs to be changeable) and the initials column are populated. (Less typing fewer errors). Hit the enter button and we move across to the right and after column G we move down a line. We have about fifty a day to type in so the data builds up fast, if they type in the same registration or serial number twice or more the cell turns red so they can investigate. There will be a search on Registration, Name or serial number from Sheet2 (Info) extracting all the data rather than taking them to the line as information could be on more than once.
If the supervisor logs in (at the moment available to all) A monthly report is available (ctrl r) but will also work from a button on Sheet2 (Info), this report extracts information from Sheet1 (Data). It firsts asks for start date, end date and account, then opens new workbook copies the information that matches. Deletes what’s not needed adds what is and then saves to new file name based on account number and month, finally displays a message box giving location and name. (Yes that end bit all works)

Returns you to original file.”

That’s it all…………. Its nearly there but not quite…….

Another one fixed only the ones in red left.....

I have attached the lastest version.

Hi MAButler

If you're addressing jaslake, sorry, I'm terribly color blind. Can't see Red (or most colors).
Give me another clue.

Hi MAButler

Code in a workbook doesn't carry special formatting. You can color it red, green, purple or blue, bold, italic, underlined what ever you please...it's my experience the formatting is not retained in the code. Could be wrong and have learning to do.

You CAN do this when you post code on the Forum...not in attachments (my experience).

I use comments when trying to give a message embedded in code.