Look If Statement

**STEVEB** · 02-13-2006, 06:16 PM

Does anyone have any suggestions to look at column A and:

In column A:
If Cell.Value = "005" Then
Colum B Cell Value="AAA"
In column A:
If Cell.Value = "102" Then
Colum B Cell Value="BBB"
In column A:
If Cell.Value = "109" Then
Colum B Cell Value="CCC"
In column A:
If Cell.Value = "112" Then
Colum B Cell Value="DDD"
In column A:
If Cell.Value = "641" Then
Colum B Cell Value="EEE"
In column A:
If Cell.Value = "2090" Then
Colum B Cell Value="FFF"

Any help would be greatly appreciated!

Mark · 02-13-2006, 07:10 PM

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NewValue As String
If Target.Column = 1 Then
Select Case Target.Text
Case Is = "005", "5"
NewValue = "AAA"
Case Is = "102"
NewValue = "BBB"
Case Is = "109"
NewValue = "CCC"
Case Is = "112"
NewValue = "DDD"
Case Is = "641"
NewValue = "EEE"
Case Is = "2090"
NewValue = "FFF"
End Select
Target.Offset(0, 1) = NewValue
End If

End Sub

This assumes that you want the Triple letters to occurr right next to
your exixting numbers.

**STEVEB** · 02-14-2006, 10:45 AM

Thanks Mark, I appreciate your help!

I receive the following error messsage:

Run Time Error 424 - Object required

At this line:

If Target.Column = 1 Then

Any sugestions?

Thanks

**avveerkar** · 02-14-2006, 11:19 AM

Originally Posted by STEVEB

Thanks Mark, I appreciate your help!

I receive the following error messsage:

Run Time Error 424 - Object required

At this line:

If Target.Column = 1 Then

Any sugestions?

Thanks

Mark's routine seems to be OK. Just check that you have copied it correctly. Also I hope the procedure is on the code sheet of your worksheet. ( Rightclick the worksheet tab and then click 'view code'. Paste the procedure given by Mark on this code sheet.) It should work.

A V Veerkar

**avveerkar** · 02-14-2006, 10:54 AM

Originally Posted by STEVEB

Does anyone have any suggestions to look at column A and:

In column A:
If Cell.Value = "005" Then
Colum B Cell Value="AAA"
In column A:
If Cell.Value = "102" Then
Colum B Cell Value="BBB"
In column A:
If Cell.Value = "109" Then
Colum B Cell Value="CCC"
In column A:
If Cell.Value = "112" Then
Colum B Cell Value="DDD"
In column A:
If Cell.Value = "641" Then
Colum B Cell Value="EEE"
In column A:
If Cell.Value = "2090" Then
Colum B Cell Value="FFF"

Any help would be greatly appreciated!

Instead of VBA you could have a nested IF formula in B1

=IF(A1="005","AAA",IF(A1="102","BBB",IF(A1="109","CCC",IF(A1="112","DDD",IF(A1="641","EEE",IF(A1="2090","FFF",""))))))
And then autofill to all the cells (B2 onward) in col B.

I am assuming that col A has text characters not numbers. eg if A1 ="102" then B1 = "BBB" asssumes A1 is character string. If it is number then change formula by removing quotes around A1 value. eg A1=102 instead of A1="102"

A V Veerkar