Nested For Next: Move to the next row after the middle loop

[Infoconstructionrecruit]

HI All

I would be grateful for the advice on the following:
I have two "For Next" Statements nested inside each other.
The outer loop takes a phone number from a spreadsheet, then looks the number up on a messaging service.
The inner loop identifies the message conversations and drops each message back into the another sheet. The messages returned need to be on the same row as the number, but each individual message needs to be in its own column.

I have no problems with the columns part of this code.
Its is the rows. I cannot get the code to move down to the next row, when the search for the next number starts.

Any advice on the above would be appreciated.

Sample of the code in question:

    For Each ContactNumbers In MessagesSheet.Range("A2:A4")
  
     SearchNumber.SendKeys ContactNumbers.Value & (ks.Enter)
   
     ch.Wait 3000
   
  
     'Returns all messages once (includes message and time of message)
     Set ResultMessages = ch.FindElementsByClass("_22Msk")
      
     For Each ResultMessage In ResultMessages
        ColNum = ColNum - 1
      
        Set TargetCell = MessagesReturnedSheet.Cells(2, ResultMessages.Count + 6 + ColNum)
        TargetCell.Value = ResultMessage.Text
     Next ResultMessage
   
     Next ContactNumbers

 

[Alex Blakenburg]

How about changing the hardcoded 2 in the row position to ContactNumbers.Row, see below:

Set TargetCell = MessagesReturnedSheet.Cells(ContactNumbers.Row, ResultMessages.Count + 6 + ColNum)

 

[Peter_SSs]

When posting vba code in the forum, please use the available code tags. It makes your code much easier to read/debug. My signature block below has more details. I have added the tags for you this time.

Another option in case the row numbers do not match is to introduce a row counter something like this.

     For Each ResultMessage In ResultMessages
        ColNum = ColNum - 1
      
        Set TargetCell = MessagesReturnedSheet.Cells(2 + rw, ResultMessages.Count + 6 + ColNum)
        TargetCell.Value = ResultMessage.Text
     Next ResultMessage
     rw = rw + 1
     Next ContactNumbers

 

[Infoconstructionrecruit]

How about changing the hardcoded 2 in the row position to ContactNumbers.Row, see below:

Set TargetCell = MessagesReturnedSheet.Cells(ContactNumbers.Row, ResultMessages.Count + 6 + ColNum)

Hi thank you for your reply. I am sure this would have worked, but I didn't this method.

 

[Infoconstructionrecruit]

Hi Thank you for the help and reply.
I had already tried this method, but the code kept failing.
It turns out I did need the row counter within the outer loop as you kindly suggested.

However the problem i was experiencing was the column counter. Each - 1 for ColNum was remembered after the outer loop moved to the next number, therefore my results were coming closer and closer to the 1st colum, until it became a negative column, which was throwing up the error.

I corrected this by resetting the ColNum to 0 on the outer loop, therefore when passed through into the inner loop -1 was only -1 and not accumulated for the number of inner loops.

Thanks again, I'll tick this post as complete.

With regards to using the code tags, please could you let me know how to do this and what you mean by code tags?
Does it put the code in the code format on the post? I could not see the option to do that when writing the original post. Please advise and I'll ensure I will do that in future.

 

[Peter_SSs]

With regards to using the code tags, please could you let me know how to do this and what you mean by code tags?
Does it put the code in the code format on the post? I could not see the option to do that when writing the original post. Please advise and I'll ensure I will do that in future.

As I said in my previous post about this: Look in my signature block below (at the bottom of every one of my posts).