Loop through cells to populate text box

Question

I have nine cells in a range that correspond with nine different text box controls on a userform.

Below are the current If statements for two of the cells and the corresponding text boxes when the userform activates.

If wsCalc.Range("CCBalance1") > 0 Then
    With RiskCalc.CCBal1
        .Visible = True
        .Value = Format(wsCalc.Range("CCBalance1"), "Currency")
    End With
End If
If wsCalc.Range("CCBalance2") > 0 Then
    With RiskCalc.CCBal2
        .Visible = True
        .Value = Format(wsCalc.Range("CCBalance2"), "Currency")
    End With
End If

Below is the For loop I was thinking of using. I have a feeling I am nowhere near close to how this should work.

For Each Cell In wsCalc.Range("CCBalance1:CCBalance9")

    'I believe this will choose the first cell in the range named above
    If Cell.Offset(0, 0) > 0 Then 

        With RiskCalc.CCBal1
            .Visible = True
            .Value = Format(wsCalc.Range("CCBalance1"), "Currency")
        End With
    End If
Next

SJR SJR · Accepted Answer · 2018-10-18T16:58:39

This is untested, but give it a try. It assumes the relationship between range name and textbox is as straightforward as it appears.

Sub x()

Dim i As Long

For i = 1 To 9
    If Range("CCBalance" & i).Value > 0 Then 'I believe this will choose the first cell in the range named above
        With RiskCalc.Controls("CCBal" & i)
            .Visible = True
            .Value = Format(Range("CCBalance" & i), "Currency")
        End With
    End If
Next i

End Sub

Loop through cells to populate text box

1 Answers