Need code for ms excel

Copper Contributor

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

@Prr1234 

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:

Vba code is untested, please backup your file.

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!

 

Hope this will help you.

 

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

This will help all forum participants.

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

@Prr1234 

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:

Vba Code is untested, please backup your file.

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.

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.
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

@Prr1234 

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

Try this code, maybe it will help you.

My understanding is no more than that when it comes to translating the project and the project itself :smile:.

Thanks for the help. But to find the strip values under different link pags/link head, it is quite difficult to work with vb code. :grinning_face_with_sweat::grinning_face_with_sweat: