[diamondw]

Hey,
im trying to create a code where inputs are the text to be encrypted and amount that the text will be shifted(dog would be fqi with a shift of 2)and the input should be in an input box.The output is going to be in excel in a 20 column by n row array.Im excluding all non lowercase letters and make sure z with a shift a 2 would be b not something crazy.Im trying to use Chr,Mid,L Case and some others codes but i just cant seem to get it right.Please help me figure out a code.Thanks!

This post has been edited by diamondw: 09 October 2011 - 03:26 PM

[Martyr2]

Well lets start by taking a single letter and working on shifting it. If we start with a string that contains all letters like so...

Dim alphabet as String
alphabet = "abcdefghijklmnopqrstuvwxyz";

And we wanted to take "d" and shift it 2 places. We first would find the index of "d" in our string using InStr...

pos = Instr(alphabet, "d")

So this is going to set "pos" to 4, the position of "d" in our alphabet string. Now that we have the position, we can shift it X number of positions by adding to the index. To make sure that we are always within the range 1 to 26, we can use the modulus operator which returns a remainder. So if we have "z" at 26 and we shift 2, we have 28 but if we mod it by 26, we have 2 which is "b".

newIndex = (pos + X) Mod 26

Now we use newIndex to fetch the proper letter from our alphabet string.

I think you can figure out the rest of this.

[diamondw]

Okay so thanks for your help but im trying to use some specific functions.The first thing is so the output is in a 20 ncolumn by n ray.

Sub NumberArray()
    'this puts up input boxes and then stores the inputs to variables
    NumRows = InputBox("Input the number of rows.")
    NumColumns = InputBox("Input the number of columns.")
        For i = 1 To NumRows ' completes 1 row at a time
            For j = 1 To NumColumns 'goes through each column 1 at time
                Cells(i, j).Value = j + (i - 1) * NumColumns
            Next j
        Next i
End Sub

The second part is so only lower case letters are included

Function Remove(x)
    tempText = LCase(x) 'change all letters to lowercase
    
    For i = 1 To Len(x) 'len is number of characters in the script
    If Asc(Mid(tempText, i, 1)) >= 97 And Asc(Mid(tempText, i, 1)) <= 122 Then '97 is ascii a and 122 is ascii z
        tempOutput = tempOutput & (Mid(tempText, i, 1))
    End If
    Next i
    Remove = tempOutput 'outputs the text
End Function

The last part is for the shift.How do i put it all together in one code?

Select Case letter
  Case "a"
    letter = Chr(97 + shiftamount)
  Case "b"
    letter = Chr(98 + shiftamount)
  Case "c"
    letter = Chr(99 + shiftamount)
  Case "d"
    letter = Chr(100 + shiftamount)
  Case "e"
    letter = Chr(101 + shiftamount)
  Case "f"
    letter = Chr(102 + shiftamount)
  Case "g"
    letter = Chr(103 + shiftamount)
  Case "h"
    letter = Chr(104 + shiftamount)
  Case "i"
    letter = Chr(105 + shiftamount)
  Case "j"
    letter = Chr(106 + shiftamount)
  Case "k"
    letter = Chr(107 + shiftamount)
  Case "l"
    letter = Chr(108 + shiftamount)
  Case "m"
    letter = Chr(109 + shiftamount)
  Case "n"
    letter = Chr(110 + shiftamount)
  Case "o"
    letter = Chr(111 + shiftamount)
  Case "p"
    letter = Chr(112 + shiftamount)
  Case "q"
    letter = Chr(113 + shiftamount)
  Case "r"
    letter = Chr(114 + shiftamount)
  Case "s"
    letter = Chr(115 + shiftamount)
  Case "t"
    letter = Chr(116 + shiftamount)
  Case "u"
    letter = Chr(117 + shiftamount)
  Case "v"
    letter = Chr(118 + shiftamount)
  Case "w"
    letter = Chr(119 + shiftamount)
  Case "x"
    letter = Chr(120 + shiftamount)
  Case "y"
    letter = Chr(121 + shiftamount)
  Case "z"
    letter = Chr(122 + shiftamount)
    End Select

Thank you