Need Help To Find Out The Input

ranjith2523

Board Regular
Joined
Apr 29, 2014
Messages
137
Office Version
  1. 365
Hi Experts,

In the below Sub test2() code if I input "Hash ("acdegilmn")" then I am getting the output message as "909832457685751".

What input I should give if I need to get the output value as "945901726134069". I tried changing the input value multiple times but I am not getting the output value as "945901726134069".
Can someone help me to get the output value "945901726134069" by changing the input value in Sub test2() code ?



VBA Code:
Sub Hash(inputString)

Dim outputNumber As Double

outputNumber = 7

Dim letters As String

letters = "acdegilmnoprstuw"

Dim index As Long
index = 0
Dim lengthOfInputString As Double

lengthOfInputString = Len(inputString)

While (index < lengthOfInputString)

outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1))) - 1)
index = index + 1

Wend
MsgBox (outputNumber)
End Sub



VBA Code:
Sub test2()

Hash ("acdegilmn")

End Sub
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
2/2
Для включення назви списку до шаблону вам потрібно додати рядок коду, щоб скопіювати це значення на відповідній аркуші шаблону перед збереженням копії. Це можна зробити за допомогою наступного рядка коду:
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
Після цього коду буде виглядати наступним чином:
Sub SaveMasterAs()
'Навчальний посібник Excel 10
Dim wb As Workbook
Dim rНазви як розділ, c як розділ, r як розділ
'Список імен та ідентифікаторів поточного файлу на аркуші1.
Встановіть rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)) 'Шлях і ім'я до основної книги, яку потрібно відкрити
для копіювання, зберегти.
Встановіть wb = Workbooks.Open(ThisWorkbook.Path & "\Template.xlsx")
Для кожного c In rNames
З wb
'Якщо вам потрібно скопіювати щось до шаблону, скористайтеся цим рядком
.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
'Шлях і ім'я для скопійованої книги
.SaveAs Filename:=ThisWorkbook.Path & "\Template Copy" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Закінчити з
Встановіть wb = ActiveWorkbook
Далі c
wb.Закрити
Кінець Під
Перегляньте, що на аркуші1 у вас є стовпець A, у якому співпадають назви, а стовпець B - ідентифікатори. Також переконайтеся, що на аркуші шаблону є клітинка A1, де ви можете скопіювати назву зі списку. Збережіть цей файл як Template.xlsx у папці, де ви збережете копії шаблону.
 
Upvote 0
2/2
Для включення назви списку до шаблону вам потрібно додати рядок коду, щоб скопіювати це значення на відповідній аркуші шаблону перед збереженням копії. Це можна зробити за допомогою наступного рядка коду:
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
Після цього коду буде виглядати наступним чином:
Sub SaveMasterAs()
'Навчальний посібник Excel 10
Dim wb As Workbook
Dim rНазви як розділ, c як розділ, r як розділ
'Список імен та ідентифікаторів поточного файлу на аркуші1.
Встановіть rNames = Worksheets("Sheet1").Range("A2", Worksheets("Sheet1").Range("A2").End(xlDown)) 'Шлях і ім'я до основної книги, яку потрібно відкрити
для копіювання, зберегти.
Встановіть wb = Workbooks.Open(ThisWorkbook.Path & "\Template.xlsx")
Для кожного c In rNames
З wb
'Якщо вам потрібно скопіювати щось до шаблону, скористайтеся цим рядком
.Worksheets("Sheet1").Range("A1").Value = c.Offset(, 1).Value 'ID
.Worksheets("Sheet1").Range("A1").Value = r.Value 'Назва зі списку
'Шлях і ім'я для скопійованої книги
.SaveAs Filename:=ThisWorkbook.Path & "\Template Copy" & c.Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Закінчити з
Встановіть wb = ActiveWorkbook
Далі c
wb.Закрити
Кінець Під
Перегляньте, що на аркуші1 у вас є стовпець A, у якому співпадають назви, а стовпець B - ідентифікатори. Також переконайтеся, що на аркуші шаблону є клітинка A1, де ви можете скопіювати назву зі списку. Збережіть цей файл як Template.xlsx у папці, де ви збережете копії шаблону.I
I am sorry, i do not understand this language, even if I translate I understand that this response is not relevant to my question. Can some please help me on this.
 
Upvote 0
In the original form of the code, I can tell there is no solution. (Ok, at least I can't see any solution)

However, this line in your code:
outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1))) - 1)

Is it possible that this line is supposed to be like the following (Mid function with the third argument)?

VBA Code:
outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1), 1)) - 1)

If this could be the case then there is a working solution: "pramodena" is the combination that returns the number you expected. Note: I am not sure if another combination could be generated by also using some letters which do not exist in the "letters".
 
Upvote 0
In the original form of the code, I can tell there is no solution. (Ok, at least I can't see any solution)

However, this line in your code:


Is it possible that this line is supposed to be like the following (Mid function with the third argument)?

VBA Code:
outputNumber = (outputNumber * 37 + InStr(letters, Mid(inputString, (index + 1), 1)) - 1)

If this could be the case then there is a working solution: "pramodena" is the combination that returns the number you expected. Note: I am not sure if another combination could be generated by also using some letters which do not exist in the "letters".
Perfect !!! Yes "pramodena" brings the output as "945901726134069". Please please please help me to understand what is the calculation logic behind this, how you quickly identify the output value "945901726134069". One of colleague applied this same "pramodena" in below excel to get the input value as "945901726134069". My only confusion is how to identify the "pramodena" to bring the output value
"945901726134069".

Book2.xlsx
ABCDE
1acdegilmnoprstuw7
2P2693710
3r99643711
4a368668370
5m13640723377
6o504706760379
7d18674150122372
8e690943554517373
9n25564911517137378
10a945901726134069370
Sheet2
Cell Formulas
RangeFormula
C2:C10C2=(C1*D2)+E2
E2:E10E2=SEARCH(B2,$A$1)-1
 
Upvote 0
It is basically reversing the formula used and starting from the result. I started from the target number and went one step back by testing each step. I tested each step by using numbers from 1 to 16 (index in the key) until finding a whole number. Of course, I used Excel cells that makes this process easier than it normally could be, and the numbers were limited, so it was not complicated than it is which is usually a very complicated process.

The most important part for you here I think, your formula was wrong as I explained. I am sure anybody can get the same result with the correct formula quickly.
 
Upvote 0
It is basically reversing the formula used and starting from the result. I started from the target number and went one step back by testing each step. I tested each step by using numbers from 1 to 16 (index in the key) until finding a whole number. Of course, I used Excel cells that makes this process easier than it normally could be, and the numbers were limited, so it was not complicated than it is which is usually a very complicated process.

The most important part for you here I think, your formula was wrong as I explained. I am sure anybody can get the same result with the correct formula quickly.
You explained clearly but I am not getting this :( based on your above sentence I need to start working from the result and I need to reverse the formula.
Can you give me a small example at least the first two steps of how you did this? I started to do something like the below and I am really not sure what data I should apply on columns B and D. It is really confusing for me. If you don't mind can you explain at least one or two steps? Really sorry to bother you.
I understand we should start apply the formula from the output value "945901726134069" e.g. 945901726134069/37 = 25564911517137, then 25564911517137/37,.. so on but not sure how to find the "promodena" input with this.

Book2.xlsx
ABCDE
1acdegilmnoprstuw7
2370
3370
4370
5370
6370
7370
8370
9370
10945901726134069370
Sheet2
Cell Formulas
RangeFormula
E2:E10E2=SEARCH(B2,$A$1)-1
 
Last edited:
Upvote 0
It is not a good way to ask the same question twice. Or you can at least refer to the other question if they are related, so we can take a look at it to not duplicate what has been done already done, and help better without wasting duplicate time. I am referring to this question (which actually contains much more information than this thread):

The following is how my approach to solving this problem. Basically, starting from the final value and applying the reversed formula. So, I started from C4, entered numbers between 1 and 16, and continue with C5 once I got TRUE in B4 which means I got the expected number. When I completed testing all at C12, I found the corresponding letters for the index numbers in C4:C12 and pull the corresponding letter by those indexes from the letters variable. Once I get the letters, I reverse them (since I started from the beginning), and finally, I got the result.

You can do the same by copying the following mini-sheet into your worksheet, deleting C4:C12 and starting entering numbers from C4 as I tried to explain.

Hope this makes sense.
1231821.xlsx
ABCDEFGH
1acdegilmnoprstuw
2
3945901726134069TestPositionLetter
425564911517137TRUE1a
5690943554517TRUE9n
618674150122TRUE4e
7504706760TRUE3d
813640723TRUE10o
9368668TRUE8m
109964TRUE1a
11269TRUE12r
127TRUE11p
13
14PositionTest numbers from 1 to 16
15TestMake sure the generated result by using the previous number is a whole number
16LetterUse MID function to get the letter in the found position
17Result (reverse)pramodena
Sheet1
Cell Formulas
RangeFormula
A4:A12A4=(A3+1-C4)/37
B4:B12B4= ROUND(A4,0)=A4
D4:D12D4=MID(A$1,C4,1)
B17B17=TEXTJOIN("",,MID(TEXTJOIN("",, D4:D12),SEQUENCE(,9,9,-1),1))
 
Upvote 1
Solution
It is not a good way to ask the same question twice. Or you can at least refer to the other question if they are related, so we can take a look at it to not duplicate what has been done already done, and help better without wasting duplicate time. I am referring to this question (which actually contains much more information than this thread):

The following is how my approach to solving this problem. Basically, starting from the final value and applying the reversed formula. So, I started from C4, entered numbers between 1 and 16, and continue with C5 once I got TRUE in B4 which means I got the expected number. When I completed testing all at C12, I found the corresponding letters for the index numbers in C4:C12 and pull the corresponding letter by those indexes from the letters variable. Once I get the letters, I reverse them (since I started from the beginning), and finally, I got the result.

You can do the same by copying the following mini-sheet into your worksheet, deleting C4:C12 and starting entering numbers from C4 as I tried to explain.

Hope this makes sense.
1231821.xlsx
ABCDEFGH
1acdegilmnoprstuw
2
3945901726134069TestPositionLetter
425564911517137TRUE1a
5690943554517TRUE9n
618674150122TRUE4e
7504706760TRUE3d
813640723TRUE10o
9368668TRUE8m
109964TRUE1a
11269TRUE12r
127TRUE11p
13
14PositionTest numbers from 1 to 16
15TestMake sure the generated result by using the previous number is a whole number
16LetterUse MID function to get the letter in the found position
17Result (reverse)pramodena
Sheet1
Cell Formulas
RangeFormula
A4:A12A4=(A3+1-C4)/37
B4:B12B4= ROUND(A4,0)=A4
D4:D12D4=MID(A$1,C4,1)
B17B17=TEXTJOIN("",,MID(TEXTJOIN("",, D4:D12),SEQUENCE(,9,9,-1),1))

First, I would like to say sorry to ask the same question in different ways in two threads. Please note that I initially raised the question in C# language in one thread and i haven't received a response at that time then i realize that this forum is specific to Excel and i shouldn't have posted C# queries. Then i converted it into Excel VBA query and posted in a new thread, later i found that i have received a response for C# thread.

I forgive my ignorance, I will not repeat this mistake in my future threads. I shouldn't have wasted many people's threads who are helping other people. Sorry again. Thanks for your help. I will try to delete the duplicate thread or mark it as complete.

Thanks for all your help and support.
 
Upvote 0
No need to be sorry and there is nothing to be forgiven. I am just trying to explain if we knew all the information in the other thread, then we could have helped much faster and without assumptions, even if the assumptions were right. No need to delete the other thread since I already provided a link to it. And actually, it also has the answer in C with the explanation. I would mark the answer as the solution in that thread by the way. Thanks for understanding the duplicate thread issue.

Glad to hear it helps. (I am assuming that I was able to explain how I found the solution above. If there is any question on it, just let me know.)
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,103
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top