Offset in a double nested loop.

[momsfavson]

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)).2621926220

[Paul_Hossler]

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

[momsfavson]

Sorry about that!

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.
26221

[Paul_Hossler]

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

You probably wanted something like this





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

[snb]

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


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