Is it possible to pull string reference to loop through declaration of new objects

[Dimaa]

Hi all,

I'm a newbie, but I'm hoping I've got a question that's interesting enough.

I'm working on a userform for data entry, writing code to insert userform entries into a worksheet, which is simple enough. But...

I expect to move columns around when doing analysis, so I do not want to hardcode the column addresses for the worksheet entries [e.g., putting replies to question #1 into column A, question #2 into column B, etc]. To do this, I've written code that finds the heading of the appropriate column [e.g. "question #1"], and then inserts the user form entry there.

Briefly, this looks something like:

Dim DataRng As Range
Dim Q1Col As Integer 

Set DataRng = Worksheet("Sheet1").Range(Cells(1,1),Cells(100,100))

Q1Col = DataRng.Find("Q1", lookat:=xlWhole).Column

Worksheet("Sheet1").Cells(1, Q1Col) = textboxQ1.Text

My problem is that I've got a lot of questions, so I'd like to loop through all of these question entries. But to keep the flexibility of identifying which column the entry goes in, I need to get column references for every question.

I thought of using a loop to pull the name of the userform controls à la:

Dim ctrl As Controls

For Each ctrl In UserForm.Controls

Dim (ctrl.Name & "Col") As Integer

Next ctrl

That defintely doesn't work. The Dim seems to resist any parentheses, and that blocks my effort to simplify writing out Dim and column references for every question individually. Or anyway, that's how it looks to me right now.

Help or suggestions much appreciated!

 

[Andrew Poulsom]

This finds the question in row 1 and puts the answer in row 2:

Private Sub CommandButton1_Click()
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim i As Long
    Dim c As Long
    Set Sh = Worksheets("Sheet1")
    With Sh
        Set Rng = .Range("A1", .Range("A1").End(xlToRight))
    End With
    With Rng
        For i = 1 To .Columns.Count
            c = .Find(What:="Q" & i, After:=.Cells(.Cells.Count), LookIn:=xlValues, LookAt:=xlWhole).Column
            .Cells(2, c).Value = Me.Controls("textboxQ" & i).Text
        Next i
    End With
End Sub

 

[Dimaa]

Thanks for that reply! It took me some time to digest it, but I tinkered and got it working in the end.

Tip o' the hat to you.

=)