Very recently, a comment on a past post with a question turned out to be a very interesting challenge. Without further wait, here is the question:
“Is it possible to do a “ceasar cipher” of three over letters (input A and output is D)
(A=D, B=E, C=F, D=G, … X=A, Y=B, Z=C)
ABCDEFGHIJKLMNOPQRSTUVWXYZ
DEFGHIJKLMNOPQRSTUVWXYZABC
e.g. I LOVE CARS = L ORVH FDUV
e.g. WKDQN BRX = THANK YOU
Please help me, I am having difficulties in doing this in excel vlookup.”
As is always the case, there are many different ways to get this done. I personally felt it would be easier with vba than using vlookups. Since I would likely not be dealing with hundreds of different messages at once, using simple loops. Here is how I represented the issue:
As you can see, I wanted to be able to both uncode a message as he had asked but also use the same one to code something else. Here is the code that I used for the first part:
“Sub decode()
i = 4
‘go line by line
Do Until Cells(i, 2).Value = “”
mlength = Len(Cells(i, 2).Value)
j = 1: k = 1
dmessage = “”
‘go through the cell, character by character
Do Until j = mlength + 1
mcharacter = Mid(Cells(i, 2).Value, j, 1)
k = 1
‘for each character, find the correct coded
If mcharacter = ” ” Then
dmessage = dmessage & ” ”
End If
Do Until k = 30
If Cells(k, 11).Value = mcharacter Then
dmessage = dmessage & Cells(k, 10).Value
End If
k = k + 1
Loop
j = j + 1
Loop
Cells(i, 3).Value = dmessage
i = i + 1
Loop
End Sub”
I did very small modifications for the second part. You can see the end result here:
And as always, feel free to download the spreadsheeet here.
***************************************************
Look Good at Work and Become Indispensable Become an Excel Pro and Impress Your Boss
***************************************************
dilawar
i always read your post whenever mail comes. its good. i have one question that how van i calculate over time e.g. 08:00 to 17:30 pm morning full day and 17:30 to 20:00pm after office time and 11:00 pm to 02:35 am i want you to solve my question as i dont know the time calculation formula.