[SOLVED:] Offset in a double nested loop.

2 Attachment(s)

Offset in a double nested loop.

Hi! I'm a beginner trying to learn VBA and am running into issues. Please try to keep your coding corrections basic and similar to what I have down! I'm not skilled enough to get ~fancy~ yet.

Here is the goal of the code:

"You are going to code a function that transcribes the array to reveal the hidden message I have arranged and output it into a cell that I designated. Make a nested loop with the outer loop counting from 1 to 7 using the variable i. The inner loop will count from 1 to 14 using the variable j. Let the variable Number get the values for cells from left to right then proceed down (i.e. Number = ActiveCell(i,j).value or some variant starting at A1). Then, on the inside loop make if statements that says
IF:
a. Number = 4 Then Letter = D
c. Number = 19 Then Letter = S
d. Number = 12 Then Letter = L
e. Number = 16 Then Letter = P... and so on
Next, make the variable Word be the sum of the variable Letter after every inside iteration. Note, the variable Word needs to be reset after every iteration of i. Finally, output the variable Word into cells A10:A16 for each i. This problem tests your knowledge on coding nested loops, if functions, usage of dimensions, and order of operations."

I mostly get a mismatch error on the bolded line, but the values in the sheet are numbers. If I don't get a mismatch error, I get that it's out of range. I get the first line to go in A10, but I cant get anything after that. Please help! Also can someone let me know if I'm able to do an offset with two variables? (i.e. offset(i, j)).Attachment 26219 Attachment 26220

Pictures are not very useful, can you attach a workbook instead?

Failing that, use the [#] icon and paste the macro between the CODE tags. That way at least people can copy it since no on wants to re-type all that.
Include what a sample input and output are

Also every line is bold

1 Attachment(s)

Sorry about that!

Code:

Sub Q1() Dim Letter As String Dim Number As Integer Dim Word As String Dim i As Integer Dim j As Integer Sheets("Q1").Activate Range("A1").Activate For i = 1 To 8 Word = "" For j = 1 To 15 Number = ActiveCell.Offset(i - 1, j - 1).Value If Number = 0 Then Letter = " " Word = Word + Letter ElseIf Number = 4 Then Letter = "D" Word = Word + Letter ElseIf Number = 19 Then Letter = "S" Word = Word + Letter ElseIf Number = 12 Then Letter = "L" Word = Word + Letter ElseIf Number = 16 Then Letter = "P" Word = Word + Letter ElseIf Number = 21 Then Letter = "U" Word = Word + Letter ElseIf Number = 25 Then Letter = "Y" Word = Word + Letter ElseIf Number = 22 Then Letter = "V" Word = Word + Letter ElseIf Number = 9 Then Letter = "I" Word = Word + Letter ElseIf Number = 1 Then Letter = "A" Word = Word + Letter ElseIf Number = 15 Then Letter = "O" Word = Word + Letter ElseIf Number = 7 Then Letter = "G" Word = Word + Letter ElseIf Number = 18 Then Letter = "R" Word = Word + Letter ElseIf Number = 23 Then Letter = "W" Word = Word + Letter ElseIf Number = 20 Then Letter = "T" Word = Word + Letter ElseIf Number = 5 Then Letter = "E" Word = Word + Letter ElseIf Number = 14 Then Letter = "N" Word = Word + Letter End If Next j Range("A10").Activate ActiveCell.Offset(i - 1, 0).Value = Word Next i End Sub

Also here is what the excel file looks like and puts out.
Attachment 26221

1 Attachment(s)

When i = 1 or j = 1 then Sheet1.Cells(i - 1, j - 1).Value give Cells (0,0)

You probably wanted something like this

Code:

For i = 1 To 7 Word = "" For j = 1 To 14 Number = Sheet1.Cells(i, j).Value

Avoid interaction with the worksheet; read the data into an array:

Code:

Sub M_snb() sn=sheet1.cells(1).currentregion for j=1 to ubound(sn) for jj=1 to ubound(sn,2) c00=c00 & chr(sn(j,jj)) next next End Sub