Aliases Cell formulas Convert to VBA

[Kieranz]

Hi All
I have in cell B2 "BLACKWHITE".
In cell C2 i have a 4 digit product code eg "BWCT" which will contain
letters in above.
In cell D2 i have the formula to rehash the code in C2.

To do that in C5 i have the formula =left(C2,1).
in C6 its =right(left(C2,2),1)
in C7 its =right(left(C2,3),1)
and C8 its =right(left(C2,4),1)
I have basically split the 4digit word into individual characters in 4
different cells.

I then have in D5 the formula which says take the value in C5 find in
B2 and take the 2 value in B2.
eg C5=B therefore in B2 its the first position taking the 2 value
gives A. So D5 =A
eg C6=W therefore D6 = I; C7=C therefore D7=W and tricker C8=T
therefore D8=B
Having rehashed the individual cells D2 will be a concatenate formula
ie =D5&D6&.....etc

The above works great in the sheet, buut i don't know how to figure it
out as a vba code.
The idea is user enters in C2 the 4digit product code and on another
sheet say cell D2 the vba should enter the rehashed product code ie
AIWB.

Your help would be much appreciated.
Thks

 

[Carl Hartness]

It took awhile to figure out that "2 value" meant the character two
positions later in "BLACKWHITE"

Try this
Sub RehashProdCode()
Dim bw$, pc$, newcode$, x%
bw$ = Range("B2") & Range("B2")
pc$ = Range("C2")
newcode$ = ""
For x% = 1 To 4
s$ = Mid(pc$, x%, 1)
newcode$ = newcode$ & Mid(bw$, InStr(1, bw$, s$) + 2, 1)
Next x%
Range("D2") = newcode$
End Sub

Carl