need loop to combobox used value

[MarioMagnus]

Hi,

I need to loop with a value from a userform combobox.
I have a comboBox, that will be filled with different items. The selected item I will use it within a loop.

For Each ComboBox_Familie.Value In NachName
        vorlageWs.Cells(x, 6) = WorksheetFunction.XLookup(ComboBox_Familie.Value, NachName, VorName)
Next ComboBox_Familie.Value

The error message are Variable required - can't start assign to this expression

I have no idea, how can I use the selected variable of the combo box for this loop.

Thanks and regards, Mario.

 

[EFANYoutube]

Firstly, I would loop the cells in the worksheet to find the value of the combobox
Or easier, use the find
You are getting this error because For Each ComboBox_Familie.Value In NachName where ComboBox_Familie.Value sets should be a variable. but you have a reference to the string text/integer(depending on your combobox setup) of the combo box.
Here's what should help you

Find and select cell to match value from combobox

Hello folks Stumped again.... :( I want to find and select a cell via VBA based on a text value picked from a userform combobox. The target value will be a text value in a single column which will NOT be sorted and may contain blanks. Is that doable at all? Thanks

www.mrexcel.com

 

[MarioMagnus]

Thanks for your help,
but I have found a solution with an other user - but the only problem that I have now is, that the cells where I need to write down the values are merged cells and I get the error message "1004 - we can't do this to a merged cell". I have removed the merged cells but I get the same error and I have no idea within the code where is the problem.

Private Sub CommandButton_Druck_Click()

    Dim vorlageWs As Worksheet, kinderWs As Worksheet
    Dim saveLocation As String
    Dim x As Long
    Dim intDaysInMonth As Integer
    Dim i As Integer
   
    Dim NachName As Range, VorName As Range
    Dim xName As String, zelleName As Range
    
   
    Set vorlageWs = Worksheets("Vorlage")
    Set kinderWs = Worksheets("KInder")

    Set NachName = kinderWs.Range("A5").CurrentRegion.Columns(2)
 
    x = 8
    xName = ComboBox_Familie.Value

    For Each zelleName In NachName.Cells
        If zelleName.Value = xName Then
           vorlageWs.Cells(x, 6).Value = zelleName.Offset(0, -1).Value
           x = x + 1
        End If
    Next zelleName
 
    vorlageWs.Cells(6, 6).Value = ComboBox_Familie.Value
    vorlageWs.Cells(3, 6).Value = ComboBox_Monat.Value
    
    saveLocation = "/Users/mario.mueller/Dropbox/Tagesfamilie/Verrechnungen/Stundenblatt_" & vorlageWs.Cells(3, 6) & "_" & vorlageWs.Cells(6, 6)
    
    Application.Wait (Now + TimeValue("0:00:01"))
    
'    With vorlageWs.PageSetup
'        .Orientation = xlPortrait
'        .PrintArea = "A1:AB53"
'        .Zoom = False
'        .FitToPagesTall = 1
'        .FitToPagesWide = 1
'    End With
'
'    vorlageWs.ExportAsFixedFormat _
'        Type:=xlTypePDF, _
'        FileName:=saveLocation, _
'        OpenAfterPublish:=True

    Unload Me
    
End Sub

 

[EFANYoutube]

Thanks for your help,
but I have found a solution with an other user - but the only problem that I have now is, that the cells where I need to write down the values are merged cells and I get the error message "1004 - we can't do this to a merged cell". I have removed the merged cells but I get the same error and I have no idea within the code where is the problem.

Private Sub CommandButton_Druck_Click()

    Dim vorlageWs As Worksheet, kinderWs As Worksheet
    Dim saveLocation As String
    Dim x As Long
    Dim intDaysInMonth As Integer
    Dim i As Integer
  
    Dim NachName As Range, VorName As Range
    Dim xName As String, zelleName As Range
   
  
    Set vorlageWs = Worksheets("Vorlage")
    Set kinderWs = Worksheets("KInder")

    Set NachName = kinderWs.Range("A5").CurrentRegion.Columns(2)
 
    x = 8
    xName = ComboBox_Familie.Value

    For Each zelleName In NachName.Cells
        If zelleName.Value = xName Then
           vorlageWs.Cells(x, 6).Value = zelleName.Offset(0, -1).Value
           x = x + 1
        End If
    Next zelleName
 
    vorlageWs.Cells(6, 6).Value = ComboBox_Familie.Value
    vorlageWs.Cells(3, 6).Value = ComboBox_Monat.Value
   
    saveLocation = "/Users/mario.mueller/Dropbox/Tagesfamilie/Verrechnungen/Stundenblatt_" & vorlageWs.Cells(3, 6) & "_" & vorlageWs.Cells(6, 6)
   
    Application.Wait (Now + TimeValue("0:00:01"))
   
'    With vorlageWs.PageSetup
'        .Orientation = xlPortrait
'        .PrintArea = "A1:AB53"
'        .Zoom = False
'        .FitToPagesTall = 1
'        .FitToPagesWide = 1
'    End With
'
'    vorlageWs.ExportAsFixedFormat _
'        Type:=xlTypePDF, _
'        FileName:=saveLocation, _
'        OpenAfterPublish:=True

    Unload Me
   
End Sub

That's exactly what I was saying to do.
When crossposting, please put the other links to your same posts in the original post, as per site rules