Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aliases Cell formulas Convert to VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Aliases Cell formulas Convert to VBA
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 On Feb 23, 5:38 am, "Kieranz" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert Array Formulas to Regular Formulas | Excel Worksheet Functions | |||
MS Query drops column aliases on certain queries | Excel Programming | |||
column aliases dropped with database query | Excel Programming | |||
column aliases dropped with database query | Excel Discussion (Misc queries) | |||
aliases | Excel Programming |