for the experts

In cells A2:A9 are the following names/titles #1 ,#2, #3, #4, #5 ,#N1, #N2
& #O2

in cells b2:b9 and then c2:c9 and then d2:d9 and so on and so I would like
to be able to save some typing amd have an auto check done. As it is right
now I type all the text and sometimes I put the wrong text in the wrong cell,
so no matter what text I enter in a cell the corresponding/related text will
get entered in the correct corresponding cell.

for example
when I enter text xxxP4 in cell B2 automatically have xxxP1 show up in
cell B5
when I enter text xxxHN1 in cell B3 automatically have xxxG2 show up in
cell B7
when I enter text xxxGN2 in cell B9 automatically have xxxHO1 show up in
cell B8
when I enter text xxxH3 in cell B6 automatically have xxxG5 show up in cell
B4
the same would then apply to each suscessive column if I enter text for that
column, I could enter any one of the possible 21 combinations in any cell and
would like that the corresponding combination autofills in the corresponding
cell

xxx will be anywhere from a 1 to 3 digit number with the potential for a
alpha character at the end the range of xxx is 7A, 73A, 8A, 9A, 10, 11, 12,
123, 1, 13, 2, 23, 3, 4, 43, 5, 53, 6, 63, 7, 73, 8, 83, 9, 93,

whenever I put P in the text, a P shows up in the corresponding cell
whenever I put a H in the text, a G shows up in the corresponding cell
whenever I put a G in the text a H shows up in the corresponding cell

there is also the potential for a letter to show up at the end of the
entered text , B, N, O or R if it is at the end of the text in the entered
cell it also has to show up in the autofilled cell.

each column is a day of the week and I decide who does what with who on each
day
if I enter text in cell that is not part of the above it doesn't look for a
match and leaves what I entered in the cell


thanks for any assistance you can provide on this Herman

One way:

Put this in your worksheet code module (right-click the worksheet tab
and choose "View Code"):

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim vSuffArray As Variant
Dim sTemp As String
Dim i As Long
vSuffArray = Array("1", "2", "3", "4", "5", "N1", "N2", "O2")
With Target
If .Count > 1 Then Exit Sub
If Not Intersect(.Cells, Range("2:9")) Is Nothing Then
If UCase(.Text) Like "*[P|G|H]*" Then
sTemp = UCase(Replace(Replace(UCase(.Text), _
"G", "h"), "H", "g"))
For i = UBound(vSuffArray) To 0 Step -1
If Right(sTemp, Len(vSuffArray(i))) = _
vSuffArray(i) Then
sTemp = Left(sTemp, Len(sTemp) - _
Len(vSuffArray(i))) & _
vSuffArray(.Row - 2)
Application.EnableEvents = False
Cells(i + 2, .Column) = sTemp
Application.EnableEvents = True
Exit For
End If
Next i
End If
End If
End With
End Sub

Note that this would result in xxxHO2 in B8 if xxxGN2 is entered in B9,
rather than xxxHO1. If you really want xxxHO1, then I'm not sure what
algorithm you're using.


In article <[email protected]>,
Herman <[email protected]> wrote:

> In cells A2:A9 are the following names/titles #1 ,#2, #3, #4, #5 ,#N1, #N2
> & #O2
>
> in cells b2:b9 and then c2:c9 and then d2:d9 and so on and so I would like
> to be able to save some typing amd have an auto check done. As it is right
> now I type all the text and sometimes I put the wrong text in the wrong cell,
> so no matter what text I enter in a cell the corresponding/related text will
> get entered in the correct corresponding cell.
>
> for example
> when I enter text xxxP4 in cell B2 automatically have xxxP1 show up in
> cell B5
> when I enter text xxxHN1 in cell B3 automatically have xxxG2 show up in
> cell B7
> when I enter text xxxGN2 in cell B9 automatically have xxxHO1 show up in
> cell B8
> when I enter text xxxH3 in cell B6 automatically have xxxG5 show up in cell
> B4
> the same would then apply to each suscessive column if I enter text for that
> column, I could enter any one of the possible 21 combinations in any cell and
> would like that the corresponding combination autofills in the corresponding
> cell
>
> xxx will be anywhere from a 1 to 3 digit number with the potential for a
> alpha character at the end the range of xxx is 7A, 73A, 8A, 9A, 10, 11, 12,
> 123, 1, 13, 2, 23, 3, 4, 43, 5, 53, 6, 63, 7, 73, 8, 83, 9, 93,
>
> whenever I put P in the text, a P shows up in the corresponding cell
> whenever I put a H in the text, a G shows up in the corresponding cell
> whenever I put a G in the text a H shows up in the corresponding cell
>
> there is also the potential for a letter to show up at the end of the
> entered text , B, N, O or R if it is at the end of the text in the entered
> cell it also has to show up in the autofilled cell.
>
> each column is a day of the week and I decide who does what with who on each
> day
> if I enter text in cell that is not part of the above it doesn't look for a
> match and leaves what I entered in the cell
>
>
> thanks for any assistance you can provide on this Herman

thanks for the info I'll give it a try over the weekend and let ya know how
it works

"JE McGimpsey" wrote:

> One way:
>
> Put this in your worksheet code module (right-click the worksheet tab
> and choose "View Code"):
>
> Private Sub Worksheet_Change(ByVal Target As Excel.Range)
> Dim vSuffArray As Variant
> Dim sTemp As String
> Dim i As Long
> vSuffArray = Array("1", "2", "3", "4", "5", "N1", "N2", "O2")
> With Target
> If .Count > 1 Then Exit Sub
> If Not Intersect(.Cells, Range("2:9")) Is Nothing Then
> If UCase(.Text) Like "*[P|G|H]*" Then
> sTemp = UCase(Replace(Replace(UCase(.Text), _
> "G", "h"), "H", "g"))
> For i = UBound(vSuffArray) To 0 Step -1
> If Right(sTemp, Len(vSuffArray(i))) = _
> vSuffArray(i) Then
> sTemp = Left(sTemp, Len(sTemp) - _
> Len(vSuffArray(i))) & _
> vSuffArray(.Row - 2)
> Application.EnableEvents = False
> Cells(i + 2, .Column) = sTemp
> Application.EnableEvents = True
> Exit For
> End If
> Next i
> End If
> End If
> End With
> End Sub
>
> Note that this would result in xxxHO2 in B8 if xxxGN2 is entered in B9,
> rather than xxxHO1. If you really want xxxHO1, then I'm not sure what
> algorithm you're using.
>
>
> In article <[email protected]>,
> Herman <[email protected]> wrote:
>
> > In cells A2:A9 are the following names/titles #1 ,#2, #3, #4, #5 ,#N1, #N2
> > & #O2
> >
> > in cells b2:b9 and then c2:c9 and then d2:d9 and so on and so I would like
> > to be able to save some typing amd have an auto check done. As it is right
> > now I type all the text and sometimes I put the wrong text in the wrong cell,
> > so no matter what text I enter in a cell the corresponding/related text will
> > get entered in the correct corresponding cell.
> >
> > for example
> > when I enter text xxxP4 in cell B2 automatically have xxxP1 show up in
> > cell B5
> > when I enter text xxxHN1 in cell B3 automatically have xxxG2 show up in
> > cell B7
> > when I enter text xxxGN2 in cell B9 automatically have xxxHO1 show up in
> > cell B8
> > when I enter text xxxH3 in cell B6 automatically have xxxG5 show up in cell
> > B4
> > the same would then apply to each suscessive column if I enter text for that
> > column, I could enter any one of the possible 21 combinations in any cell and
> > would like that the corresponding combination autofills in the corresponding
> > cell
> >
> > xxx will be anywhere from a 1 to 3 digit number with the potential for a
> > alpha character at the end the range of xxx is 7A, 73A, 8A, 9A, 10, 11, 12,
> > 123, 1, 13, 2, 23, 3, 4, 43, 5, 53, 6, 63, 7, 73, 8, 83, 9, 93,
> >
> > whenever I put P in the text, a P shows up in the corresponding cell
> > whenever I put a H in the text, a G shows up in the corresponding cell
> > whenever I put a G in the text a H shows up in the corresponding cell
> >
> > there is also the potential for a letter to show up at the end of the
> > entered text , B, N, O or R if it is at the end of the text in the entered
> > cell it also has to show up in the autofilled cell.
> >
> > each column is a day of the week and I decide who does what with who on each
> > day
> > if I enter text in cell that is not part of the above it doesn't look for a
> > match and leaves what I entered in the cell
> >
> >
> > thanks for any assistance you can provide on this Herman
>