CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 13

VBA Loop with Cell Values

I am pretty new to vba and I am facing a problem I couldn't find a solution to so far. I have two lists of names in the worksheet "Source" that I want to use for a for each loop. How can I address those cells by using the correct Worksheet?

I want to access combo boxes that are named "Boxvariablename" (e.g. BoxIAA) and associated text boxes in the form "variablenamevalue" (e.g. IAAvalue) and check the content of all these objects, deleting the two cells to the right (e.g. D3:E3 or G5:H5) in the worksheet "Source" if the objects are empty.

My attempt was: 

Dim rng As Range, cell As Range
Set rng = Range(Sheets("Source").Range("C2:C4"), Sheets("Source").Range("F2:F5"))


For Each cell In rng
    If "Box" & cell.Value <> "" Then

        MsgBox "The value is " & "Box" & Range(cell).Value

    Else If
        'Delete two cells to the right in ws "Source"
    End If  

Next cell

I am aware, that I am not addressing the Cells C2:C4 in the worksheet Source correctly, but I really don't know how to do it properly. How can I access the content of the source cells and address the content / the cells for later use?

Upvotes: 1

Views: 59

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Is this what you are trying (untested)?

Sub Sample()
    Dim rng As Range
    Dim aCell As Range

    On Error GoTo Whoa '<~~ Need this in case it couldn't find the control

    Set rng = Sheets("Source").Range("C2:C4,F2:F5")

    For Each aCell In rng
        '~~> Use Controls() to work with the control
        With Me.Controls("Box" & aCell.Value)
            If .Value <> "" Then
                MsgBox .Value
            Else
                '~~> Do what you want
                Sheets("Source").Range(aCell.Offset(, 1), _
                                       aCell.Offset(, 2)).Delete shift:=xlLeft
            End If
        End With
    Next aCell

    Exit Sub
Whoa:
    MsgBox Err.Description
End Sub

Instead of If .Value <> "" Then you can also use If .ListIndex <> -1 Then. I am assuming that there are no blank values in the combobox.

Upvotes: 1

Related Questions