0 votes
in Education by (1.7m points)
Below, I have code that sends a personalized SMS message and includes the name. I got that part to work. Now, I just need to make it so that my range is dynamic and moves down the respective column until there is no one left to message. In it's current state, it will only message the first person. I tried looking up dynamic range tutorials, loops, etc. but they were either too complex for me to grasp or would require me to rewrite what I already have working.

Private Sub btnSend_Click()

   Dim contactNumberRange As Range

   Dim messageRange As Range

   Dim clientNameRange As Range

   Dim phoneCell As Range

   Dim messageCell As Range

   Dim nameCell As Range

   Set contactNumberRange = Range("D2") //Need to make this range dynamic

   Set messageRange = Range("E2") //This too

   Set clientNameRange = Range("A2") //This aswell

   For Each phoneCell In contactNumberRange

    For Each messageCell In messageRange

     For Each nameCell In clientNameRange

      SendMessage FROMPHONE, nameCell.Value, phoneCell.Value, messageCell.Value

     Next

    Next

   Next

 Me.Hide

End Sub

JavaScript questions and answers, JavaScript questions pdf, JavaScript question bank, JavaScript questions and answers pdf, mcq on JavaScript pdf, JavaScript questions and solutions, JavaScript mcq Test , Interview JavaScript questions, JavaScript Questions for Interview, JavaScript MCQ (Multiple Choice Questions)

1 Answer

0 votes
by (1.7m points)
You just need to ammend your ranges with a last row variable.

Also, qualify those ranges with a worksheet!

Dim ws As Worksheet: Set ws = ThisWorkbook.Sheets("Sheet1")

Dim LR As Long

LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row

Set contactNumberRange = ws.Range("D2:D" & LR)

Set messageRange = ws.Range("E2:E" & LR)

Set clientNameRange = ws.Range("A2:A" & LR)

For Each phoneCell In contactNumberRange

    For Each messageCell In messageRange

        For Each nameCell In clientNameRange

            SendMessage FROMPHONE, nameCell.Value, phoneCell.Value, messageCell.Value

        Next nameCell

    Next messageCell

Next phoneCell

Related questions

0 votes
1 answer
asked 4 days ago in Education by JackTerrance (1.7m points)
0 votes
1 answer
0 votes
1 answer
...