PDA

View Full Version : INPUT AND RESULT



pbdsp79
10-26-2007, 08:47 AM
Hi All


Having a excel file, in which there are two sheets, one is INPUT and other is RESULTS.
In input sheet A1 is named as CUSIP, under which we input various alphnumeric figures, and in results sheet, we want that all alphanumeric figures should turn into 9 digits.
for eg.in input if it is 123456, than in result it should become 000123456 ie 9 digits.
for eg.if we input asd78 in input sheet, it should reutrn us 0000asd78
I have tried from my side, but i am not able to find solution, seeking help form you.
I am attaching excel file for ur reference, in which the way i want the results is also there, that is i have manually inserted zeros to make it 9 alphanumeric, now i want that once i input alphanumeric digits in input ssheet, it shoudl automatically change to 9 alpahnumeric digit in reult screen.
Regards
Prashant

Bob Phillips
10-26-2007, 09:18 AM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "A:A" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
If Target.Count = 1 Then
If Target.Row > 1 Then
Worksheets("RESULTS").Range(Target.Address(0, 0)).Value = _
"'" & Left$("000000000", 9 - Len(Target.Value)) & Target.Value
End If
End If
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

lucas
10-26-2007, 09:20 AM
Hi Prashant,
This came up recently and a knowledgbase article was written..I think it might be just what you're looking for. Click here (http://vbaexpress.com/kb/getarticle.php?kb_id=946).