Consulting

Results 1 to 5 of 5

Thread: Offset in a double nested loop.

  1. #1

    Question 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)).Image 3-26-20 at 9.34 AM.jpgImage 3-26-20 at 9.36 AM.jpg
    Last edited by momsfavson; 03-26-2020 at 09:20 AM.

  2. #2
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,000
    Location
    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
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    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.
    Screen Shot 2020-03-26 at 11.07.41 AM.jpg

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,000
    Location
    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
    Attached Files Attached Files
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •