Encrypting Project

**Drus** · 01-31-2011, 02:34 AM

Hello there,

The Encrypting.xlsm worksheet is a project that encrypts text, turning letters into numbers, numbers that are produced by assigning a key that provide variables to complex excel calculations.

Its purpose is to automatically "encrypt" or translate letters to words or vice-versa, according to the key used.

I need a button in the "Encryptor" sheet that is assigned with a macro that when triggered:

Will take the text, letter by letter, within a cell and paste the equivalent number for each letter into another cell.

And.. Will do the reverse process simultaneously: Take the encrpyted message from another cell, number by number (separated by a space), and paste the equivalent letter for each number into another cell.

The numbers equivalent for each letter are shown on column D from row 2 to row 28, in the "number" sheet.

I had a tough time imagining how would this project work and spent some additional effort preparing the formulas. All I need is the macro, which is quite a challenge.

If you could lend me a hand on the macro it would be great, and you'll have an encryption worksheet for your own!

**Drus** · 01-31-2011, 02:52 AM

On other details of project:

> I have ensured numbers equivalent to letters will never surpass the 2 digits length.
> Numbers in a set will never repeat; like for example: A = 45, F =45.
> It is not possible to decipher anything from the text without the encrypting.xlsm excel document.
> In case someone gets your decyphering document, you can easily update the letter sheet into a different random order.

I'll get on and try to do the macro by myself too although I am at "noob" level at VBA coding.

**Drus** · 01-31-2011, 11:15 AM

The structure of a macro that allows me to "encrypt" should be something like:

1. Get Text from a specified cell.
2. If "A", then return the value of a specified cell into another cell, if not: If "B", then return the value of a specified cell, if not: If "C"... (all the way to "Z")... if not: then display "Error".

It should be a macro which resembles the IF formula for excel.

The structure of a macro that allows me to "decipher" should be something like:

1. Get the number and the space (from example 56 , or 56" ") from a specified cell.
2. Look up for that number in a specified range, if the cell in which the value is found is in row #, then return "A"...

**Drus** · 01-31-2011, 11:19 AM

Another alternative could be a macro that:

To encrypt:

1. Copies the text from the input cell to the output cell.
2. Then replaces each letter in the output cell by the value of a specified cell.

To decipher:

1. Copies the value from the input cell to the output cell.
2. Then gets the first number separated by a space, then looks in a specified range of cell for that value, and according to what cell is found replace it with a letter.

**Drus** · 01-31-2011, 04:57 PM

This is how my current project looks like, still no macros for it.

I keep researching for the bits and pieces, like conditional macros needed, text and replace, select cell value macros...