Using a loop in Excel/VBS to populate a form

Question

Please can somebody help me with the correct DIM statements and syntax to simplify the following into a DO UNTIL loop?:

Sub DesRisk_Loader()

Dim Qn(7) As String
Dim Ys(7) As String
Dim No(7) As String
Dim Wk(7) As Integer

Application.ScreenUpdating = False

n = 1
x = 1
Do
Application.Goto Reference:="DesHome"
    ActiveCell.Offset(x, 0).Select
    Qn(n) = ActiveCell.Value

    ActiveCell.Offset(0, 1).Select
    Ys(n) = ActiveCell.Value

    ActiveCell.Offset(0, 1).Select
    No(n) = ActiveCell.Value

    ActiveCell.Offset(0, 1).Select
    Wk(n) = ActiveCell.Value
    x = x + 2
    n = n + 1
Loop Until n = 8

''Q.1
If Qn(1) <> "" Then
    DesForm.DesFrame1.Visible = True
    DesForm.Dq1.Caption = Qn(1)
    FH = 0
    If Ys(1) = "P" Then
        DesForm.D1y.Value = True
        Else
        DesForm.D1y.Value = False
    End If
    If No(1) = "O" Then
        DesForm.D1n.Value = True
        Else
        DesForm.D1n.Value = False
    End If
    DesForm.DesDly1.Value = Wk(1)
Else:
    Exit Sub
End If

''Q.2
If Qn(2) <> "" Then
    DesForm.DesFrame2.Visible = True
    DesForm.Dq2.Caption = Qn(2)
    FH = 1
    If Ys(2) = "P" Then
        DesForm.D2y.Value = True
        Else
        DesForm.D2y.Value = False
    End If
    If No(2) = "O" Then
        DesForm.D2n.Value = True
        Else
        DesForm.D2n.Value = False
    End If
    DesForm.DesDly2.Value = Wk(2)
Else: GoTo Jump1
End If

''Q.3
If Qn(3) <> "" Then
    DesForm.DesFrame3.Visible = True
    DesForm.Dq3.Caption = Qn(3)
    FH = 2
    If Ys(3) = "P" Then
        DesForm.D3y.Value = True
        Else
        DesForm.D3y.Value = False
    End If
    If No(3) = "O" Then
        DesForm.D3n.Value = True
        Else
        DesForm.D3n.Value = False
    End If
    DesForm.DesDly3.Value = Wk(3)
Else: GoTo Jump1
End If
 ditto till..

''Q.7
If Qn(7) <> "" Then
    DesForm.DesFrame7.Visible = True
    DesForm.Dq7.Caption = Qn(7)
    FH = 6
    If Ys(7) = "P" Then
        DesForm.D7y.Value = True
        Else
        DesForm.D7y.Value = False
    End If
    If No(7) = "O" Then
        DesForm.D7n.Value = True
        Else
        DesForm.D7n.Value = False
    End If
    DesForm.DesDly7.Value = Wk(7)
Else: GoTo Jump1
End If

Jump1:
DesForm.Height = 140 + (FH * 75)
DesForm.DesOK.Top = 85 + (FH * 75)
DesForm.DesCancel.Top = 85 + (FH * 75)

Load DesForm
DesForm.Show

End Sub

Thanks Scott

This whole process feels unneccesarily complicated. Like Raj More said, what's the big picture here? There's probably a simpler solution. — PowerUser
– PowerUser, Commented Aug 16, 2010 at 14:31

Raj More · Accepted Answer · 2010-08-16 01:09:53Z

2

At the top of your code (First Line in the entire module), type the following OPTION EXPLICIT

That will help identify all undeclared variables.

answered Aug 16, 2010 at 1:09

Raj More

48.2k34 gold badges139 silver badges199 bronze badges

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

Collectives™ on Stack Overflow

Using a loop in Excel/VBS to populate a form

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related