# Need code for ms excel

Copper Contributor

# Need code for ms excel

I need help for the following. Lets see an examples

Suppose we have digits 0 to 9 only. If we are assigning any four digit number like 7856 as 2801 ( In this case we always alott 2801 to any desired four digit number like here 7856) then accordingly code will detect 7 as 2, 8 as 8, 5 as 0 and 6 as 1. After that we entered another four digit number ex. 5691 to another cell and the program will detect the assigned number to corresponding digit if found otherwise leave blank to the corresponding rows. Please tell me the code to make program for such case.

7 Replies

# Re: Need code for ms excel

Based on your description, it sounds like you want to create a program in Excel VBA that assigns specific replacements for each digit and then applies these replacements to any four-digit number entered into a cell. Here's a basic implementation of such a program:

``````Sub ReplaceDigits()
Dim digitMap(0 To 9) As Integer
Dim inputNumber As String
Dim outputNumber As String
Dim i As Integer

' Define the replacements for each digit
digitMap(0) = 2
digitMap(1) = 8
digitMap(2) = 0
digitMap(3) = 1
digitMap(4) = 9
digitMap(5) = 7
digitMap(6) = 4
digitMap(7) = 3
digitMap(8) = 6
digitMap(9) = 5

' Get the input number from the user
inputNumber = InputBox("Enter a four-digit number:")

' Check if the input number is valid
If Len(inputNumber) <> 4 Or Not IsNumeric(inputNumber) Then
MsgBox "Invalid input. Please enter a four-digit number."
Exit Sub
End If

' Replace each digit in the input number using the digitMap
outputNumber = ""
For i = 1 To Len(inputNumber)
outputNumber = outputNumber & CStr(digitMap(CInt(Mid(inputNumber, i, 1))))
Next i

' Output the result
MsgBox "Mapped number: " & outputNumber
End Sub``````

To use this code:

1. Press Alt + F11 to open the VBA editor in Excel.
2. Go to Insert > Module to insert a new module.
3. Copy and paste the code into the module window.
4. Close the VBA editor.
5. You can now run the ReplaceDigits macro by going to Developer > Macros (if Developer tab is not visible, you can enable it in Excel options) and selecting ReplaceDigits from the list, then click Run.

This code prompts the user to input a four-digit number, applies the digit replacements according to your specifications, and then displays the mapped number in a message box. You can further modify it to apply the replacements to any range of cells or integrate it into your Excel workbook as needed. The text, steps and the code was created with the help of AI.

My answers are voluntary and without guarantee!

Was the answer useful? Mark as best response and like it!

This will help all forum participants.

# Re: Need code for ms excel

Thanks for the reply but my concern is that the decrypted key is 2801 and any 4 digit number entered by the user will be assigned to 2801 as decrypted key and as per that remaining number from 0 to 9 will be decoded if available or leave blank space si that later a strip of decrypted keys could be find manually

# Re: Need code for ms excel

If I understand correctly, you want to create a VBA code that allows a user to enter a four-digit number, and the program will decode each digit of that number based on a predefined decryption key (2801 in this case). If a digit is not specified in the decryption key, it will remain unchanged.

Here's a VBA code that implements this functionality:

``````Sub DecryptNumber()
Dim decryptionKey As String
Dim inputNumber As String
Dim decryptedNumber As String
Dim i As Integer

' Define the decryption key
decryptionKey = "2801"

' Get the input number from the user
inputNumber = InputBox("Enter a four-digit number:")

' Check if the input number is valid
If Len(inputNumber) <> 4 Or Not IsNumeric(inputNumber) Then
MsgBox "Invalid input. Please enter a four-digit number."
Exit Sub
End If

' Decrypt each digit in the input number using the decryption key
decryptedNumber = ""
For i = 1 To Len(inputNumber)
If Mid(decryptionKey, i, 1) <> "" Then
decryptedNumber = decryptedNumber & Mid(decryptionKey, i, 1)
Else
decryptedNumber = decryptedNumber & Mid(inputNumber, i, 1)
End If
Next i

' Output the decrypted number
MsgBox "Decrypted number: " & decryptedNumber
End Sub``````

This code prompts the user to enter a four-digit number, decrypts each digit of that number based on the decryption key (2801), and then displays the decrypted number in a message box. If a digit is not specified in the decryption key, it remains unchanged.

# Re: Need code for ms excel

Code is working fine. Thanks
But still there is some problem. That is it shows any 4 digit number to 2801 then combination of other 4 digit number should also automatically assigned accordingly as 2801. For example we have combination of 3 set of 4 digit number for decryption viz, first 8734 , second 3724, third 4425 and now we should assign each digit of the first number i.e. 8734 to 9501 and accordingly shows decrypted key for the remaining set of numbers.

# Re: Need code for ms excel

vb code to make program to take any digit and assign it as 9, 5, 0 and 1 and stored it and then again take another group of four digit number and assign each digit accordingly as per 9,5,0,1 if found or leave a (.) In that place but the first group of four digit number should be assigned to 9501 then take another four digit number and if the digit repeats it shows 9 or 5 or 0 or 1 or leave a (.) in that place so that a set of decrypted keys could be traced

# Re: Need code for ms excel

``````Dim decryptionKey As String

Function EncryptNumber(inputNumber As String) As String
Dim encryptedNumber As String
Dim i As Integer
decryptionKey = "9501" ' Set the initial decryption key

For i = 1 To Len(inputNumber)
encryptedNumber = encryptedNumber & GetEncryptedDigit(Mid(inputNumber, i, 1))
Next i

EncryptNumber = encryptedNumber
End Function

Function GetEncryptedDigit(digit As String) As String
If InStr(decryptionKey, digit) > 0 Then
GetEncryptedDigit = Mid(decryptionKey, InStr(decryptionKey, digit), 1)
Else
GetEncryptedDigit = "."
End If
End Function

Function DecryptNumber(encryptedNumber As String) As String
Dim decryptedNumber As String
Dim i As Integer

For i = 1 To Len(encryptedNumber)
decryptedNumber = decryptedNumber & GetDecryptedDigit(Mid(encryptedNumber, i, 1))
Next i

DecryptNumber = decryptedNumber
End Function

Function GetDecryptedDigit(encryptedDigit As String) As String
Dim index As Integer
index = InStr(decryptionKey, encryptedDigit)

If index > 0 Then
GetDecryptedDigit = Mid("1234567890.", index, 1)
Else
GetDecryptedDigit = "."
End If
End Function``````