Unable to figure out VBA Code needed

[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 give "AIWB".

To hash; 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 then take the second(2) value in B2.
eg C5=B therefore in B2 its the first position taking the second 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 worksheet, but i just 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 will enter the rehashed product code ie
AIWB.

Your help would be much appreciated.
Thks

 

[Guest]

try:


in D2: =HashCode(B2,C"2)

HTH

Function HashCode(ByRef mystr As String, ByRef pcode As String)
Dim length As Integer, i As Integer, n As Integer
Dim mychar As String, hscode As String
length = Len(mystr)
hscode = ""
For i = 1 To 4
mychar = Mid(pcode, i, 1)
n = InStr(1, mystr, mychar)
If n + 2 > length Then n = -1
hscode = hscode + Mid(mystr, n + 2, 1)
Next i
HashCode = hscode
End Function

 

[Carl Hartness]

I replied to your identical post in
http://groups.google.com/group/microsoft.public.excel.programming/t/3a770bea382837dd?hl=en

Toppers' solution is a function with passed parameter, which is more
flexible. The suggestion I had that is different is to concatenate
two of the "BLACKWHITE" strings, to not need the check for the end two
positions. That step is only done once, instead of a check every time
through the loop.

Carl.

 

[Kieranz]

I replied to your identical post inhttp://groups.google.com/group/microsoft.public.excel.programming/t/3...

Toppers' solution is a function with passed parameter, which is more
flexible. The suggestion I had that is different is to concatenate
two of the "BLACKWHITE" strings, to not need the check for the end two
positions. That step is only done once, instead of a check every time
through the loop.

Carl.

Many thks to Carl and Topper. Sorry could not come earlier. Will
study this over the weekend if work doesn't take me away.
Rgds
KZ