VBA Loop with Cell Values

Question

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?

Note that the Range in Set rng = Range( is not itself qualified by the worksheet. In order to save on typing, why not declare and set a worksheet variable ws? Also, if cell is a range object then Range(cell) is pointless. — John Coleman
– John Coleman, Commented Feb 22, 2019 at 11:02

Siddharth Rout · Accepted Answer · 2019-02-22 11:08:44Z

1

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.

edited Feb 22, 2019 at 11:08

answered Feb 22, 2019 at 11:03

Siddharth Rout

150k18 gold badges212 silver badges258 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

Thank you so much Siddharth Rout, that solved the issue! I really had no plan on how to solve it! Works great

Collectives™ on Stack Overflow

VBA Loop with Cell Values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related