VBA For each Loop - select dropdown and call another macro

[Melimob]

Hi there,

I have the below code which *should* select a dropdown for all in list and call another macro (which creates a PDF and email) but it keeps staying on the same selection producing 20 emails for the one customer?

Any ideas on what I am doing wrong?

Sub All_Intro_emailPDFLoop()
    Dim c As Range
    Dim Rng As Range
    Set Rng = Range("nDataVal_Introducers_Act_COMBO")
    For Each c In Rng
        Range("G6").Select
        Call emailsavePDF_Intro
    Next
End Sub

 

[Fluff]

Best guess is change Range("G6").Select to c.Select

 

[Melimob]

thanks Fluff but I get error "select method of range class failed'?
it's in a combobox active x where the dropdown is and when something in that is selected, it updates the formulas on the page.
macro then creates pdf and email.

 

[Fluff]

Is the sheet protected?

 

[Melimob]

Is the sheet protected?

no it's not.

I saw this code which I'm trying to adapt which my be better as user would hit a command button when they want to run it but getting an error also with this..
Error 'Variable not defined'

Option Explicit

Private Sub CommandButton1_Click()

  For rep = 0 To (Sheet21.ComboBox1.ListCount - 1)
      .Select
      Call emailsavePDF_Intro
  Next rep
    
End Sub

 

[Fluff]

You need to declare the variable rep

 

[Melimob]

You need to declare the variable rep

Hi Fluff

Apologies I'm really not sure what I'm doing I tried this and it produced 20 emails the same without changing the combobox value each time?

Option Explicit

Private Sub CommandButton1_Click()

Dim rep As Long

  For rep = 0 To (Sheet21.ComboBox1.ListCount - 1)
      ComboBox1.Select
      Call emailsavePDF_Intro
  Next rep
    
End Sub

Essentially, instead of a user changing the drop down (in the combo box) and then clicking a button to send as PDF, I want it to loop through and create the email for each client in the list (comobox active x).

It has to be selected as the name is on the PDF and the lookups change from the combobox value (linked cell).

Many thanks

 

[Fluff]

Maybe

Private Sub CommandButton1_Click()

   Dim rep As Long
   With Sheet21.ComboBox1
      For rep = 0 To .ListCount - 1
          .Value = .List(rep)
          Call emailsavePDF_Intro
      Next rep
   End With
End Sub

 

[Melimob]

Maybe

Private Sub CommandButton1_Click()

   Dim rep As Long
   With Sheet21.ComboBox1
      For rep = 0 To .ListCount - 1
          .Value = .List(rep)
          Call emailsavePDF_Intro
      Next rep
   End With
End Sub

THANK YOU THANK YOU THANK YOU!

That worked however as some of the clients don't have any data to report, 'the item cannot be found in the OLAP cube' error comes up. I feel I need to include an 'On Error Resume Next' but unsure where to place this line?
Also, I only want the report to run on the clients which are active. There is a cell which returns 'Active'/'Inactive' when the dropdown changes via a lookup however I tried to add this but got an error?:

Private Sub CommandButton1_Click()

   Dim rep As Long
  
    With Sheet21.ComboBox1
      For rep = 0 To .ListCount - 1
           .Value = .List(rep)
        If .Range("S1").Value = "Active" Then
          Call emailsavePDF_Intro
       End If
      
    On Error Resume Next
      
      Next rep
        
        
   End With
End Sub

 

[Fluff]

As this is now a significantly question, it needs a new thread. Thanks