Equidistant Letter Sequence with Excel VBA

[cairo95]

Hello,

i am looking for a looping VBA code that loops through a 2 dimensional array...lets say with 9 rows and 17 columns (label name Area1)....i would like to designate the character number to start (like start at the first character) then to skip x amount of characters and print the character in between the skipped characters....then start with the next character after the printed character and so forth...looping until the number of character print is completed (9x17 = 153 characters max) i would like the characters printed in the same array size (9x17). next to area1.


Enter start point, enter skip amount
loop through the array and print next value
I have attached an image example.

thanks in advance!
Rene

 

[Peter_SSs]

How did you find the sentences solution - just by trial and error with the 2 variables?

Two further things to consider

1. Investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with. We can't copy anything from an image like you posted.
   
   
2. Update your Excel version as i suggested in post #16. After all, IF you have Microsoft 365 you might consider solving this puzzle with worksheet formulas rather than vba with one of these formulas.

cairo95.xlsm
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Start at	Skip		S	N	P	H	H	U	A	T	V	S	B	K	W	U	J	Z	W
3	1	15		B	Y	J	N	T	B	P	M	H	Q	Q	G	H	V	V	P	Z
4				X	F	S	Z	G	N	C	Z	R	A	O	C	C	U	H	B	H
5				J	H	Y	Z	K	H	E	E	Q	K	K	S	I	Q	F	E	P
6				C	L	X	G	U	J	H	X	Q	Q	L	C	O	S	X	V	A
7				O	X	L	R	N	N	M	J	K	H	B	G	Z	B	K	J	M
8				E	M	G	Q	O	E	Y	R	N	K	C	U	L	T	P	V	A
9				F	B	C	I	D	A	N	R	O	V	C	E	R	L	J	D	S
10				Y	N	C	T	K	M	M	F	I	C	P	E	Y	C	L	H	W
11																						SWPHQOGCVITPNKGLMFSHJVCSLHNRMUTZYLOMVJCWGOKQJYAKHJFJPVKTREBQREHNOICNBZBFSZUCCVMCHURGBYWZVUICBKOFABGZXXEADLUHCKQKRNMHNSHCAJPLYKQAQXMEDTPYXHEXBLEPSHZEJNQCN
12																						S W P H Q O G C V I T P N K G L M F S H J V C S L H N R M U T Z Y L O M V J C W G O K Q J Y A K H J F J P V K T R E B Q R E H N O I C N B Z B F S Z U C C V M C H U R G B Y W Z V U I C B K O F A B G Z X X E A D L U H C K Q K R N M H N S H C A J P L Y K Q A Q X M E D T P Y X H E X B L E P S H Z E J N Q C N
Sheet3

Cell Formulas
Range	Formula
W11	W11	=LET(t,CONCAT(E2:U10),n,LEN(t),CONCAT(MID(t,MOD(SEQUENCE(n,,B3-1,C3+1),n)+1,1)))
W12	W12	=LET(t,CONCAT(E2:U10),n,LEN(t),TEXTJOIN(" ",1,MID(t,MOD(SEQUENCE(n,,B3-1,C3+1),n)+1,1)))

 

[cairo95]

thank you for the suggestions! yes, a colleague of mine said this is a 340 character cipher with 3 parts from the Zodiac killer (it was an Aussie who originally solve it!) that was recently solved but we didnt know how...we wanted to see if the ELS theory held up and it appears it has... with a few tweaks...there are many misspellings so it is hard to do this manually ....i will add a macro to match the sentences for the final output.

thanks again!
Rene