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

Dimaa

New Member
Joined
Apr 2, 2011
Messages
8
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:

Code:
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:

Code:
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!
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This finds the question in row 1 and puts the answer in row 2:

Code:
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

New Member
Joined
Apr 2, 2011
Messages
8
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.

=)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,032
Messages
5,526,354
Members
409,697
Latest member
christopherlewis1620

This Week's Hot Topics

Top