Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to figure out VBA Code needed
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to figure out VBA Code needed
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 "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 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to figure out VBA Code needed
I replied to your identical post in
http://groups.google.com/group/micro...382837dd?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. On Feb 24, 5:55 am, Toppers wrote: 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 "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 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Unable to figure out VBA Code needed
On Feb 27, 2:18 pm, "Carl Hartness" wrote:
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. On Feb 24, 5:55 am, Toppers wrote: 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 "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 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- Hide quoted text - - Show quoted text - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I WANT TO WRITE 18 DIGITS FIGURE IN A SINGLE CELL. bu i m unable. | Excel Discussion (Misc queries) | |||
Formula needed to show the second to last figure in range of cells | Excel Worksheet Functions | |||
UDFs needed I think they're pretty easy but I can't figure them ou | Excel Programming | |||
Code to Save As, then Open - can't figure it out! | Excel Discussion (Misc queries) | |||
Can anyone figure this code problem please | Excel Discussion (Misc queries) |