Forum Discussion

Prr1234's avatar
Prr1234
Copper Contributor
Feb 27, 2024

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.

excel
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Feb 27, 2024

    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.

    • Prr1234's avatar
      Prr1234
      Copper Contributor
      Feb 27, 2024
      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
      • NikolinoDE's avatar
        NikolinoDE
        Gold Contributor
        Feb 28, 2024

        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.

Resources