How can I use variable cell names for form data?

[rogue.phoenix]

Here's the setup. I have a sheet called Cards with several boxes for name, address, etc. that all need to be the same. I have created a form to gather the information, have the command button code written, know the upper and lower limits (1 to 10) etc, but I cannot get the data from the txtName field to populate across the variable cell names (ie Name1,Name2, ..., Name 10).

Below is the most recent code tried... I know there is nothing assigning the tempName.Value to Name(i)... That's the big problem... Please don't laugh too hard if it is completely wrong...

Dim tempNum As String

i = 1

For i = 1 To 10
tempNum = i
tempName = "Cards.Name" + tempNum
tempName = txtName.Value
tempAdd = "Cards.Address" + tempNum
tempAdd = txtAddress.Value
tempTit = "Cards.Title" + tempNum
tempAdd = txtTitle.Value
tempPhn = "Cards.Phone" + tempNum
tempPhn = txtPhone.Value
tempEma = "Cards.Email" + tempNum
tempEma = txtEmail.Value
​

Next
​

Any advice is appreciated, and if there is already a question related to this, I am sorry, but my search of the forums turned up nothing...

 

[jim may]

Something like this would work

Private Sub CommandButton1_Click() ' Your Command Button
NR = Range("B" & Rows.Count).End(xlUp).Row + 1 'Find the next Blank Row to populate
Cells(NR, 2).Value = UserForm1.TextBox1
Cells(NR, 3).Value = UserForm1.TextBox2
Cells(NR, 4).Value = UserForm1.TextBox3
Cells(NR, 5).Value = UserForm1.TextBox4
Cells(NR, 6).Value = UserForm1.TextBox5
End Sub

 

[rogue.phoenix]

Thank you for the information, but since this didn't work either, perhaps I was not clear about the situation. Perhaps a bit more information is needed... I have named cells in non consecutive rows and multiple columns. The names are "Name1", "Name2", etc... I need to know how to utilize those names in the VBA script so it automatically populates the same information into all of those named cells from the form data. My original idea was to use something like:

for i = 1 to 10
sheet1.Name(i).Value = frmMain.txtName
next i

The problem is, VBA will not allow me to put in the (i) to utilize the variable... Keeps saying "Compile Error: wrong number of arguments or invalid property assignment."

I am trying to find a way to let the for loop do the counting and assign that integer to the end of Name so it becomes Name1, or Name2, or whatever and can have the value of txtName assigned to it.

Hopefully this cleared up any misunderstandings and will assist with any future replies.

 

[rogue.phoenix]

Thank you to everyone who at least looked into this question, but I have resolved it. This may not be the best resolution, but since the cells were evenly spaced, the following code worked...

i = 5
For v = 3 To 6

For i = 5 To 55

Cells(i, v).Value = txtName.Text
i = i + 1
Cells(i, v).Value = txtTitle.Text
i = i + 2
Cells(i, v).Value = txtAddress.Text
i = i + 1
Cells(i, v).Value = txtPhone.Text
i = i + 1
Cells(i, v).Value = txtEmail.Text
i = i + 5
​

Next
v = v + 2
​

Next

 

[shg]

Another way:

    Dim i           As Long
    Dim v           As Long
 
    With WorksheetFunction
        For v = 3 To 6 Step 3
            For i = 5 To 55 Step 11
                Cells(i, v).Resize(2).Value = .Transpose(Array(txtName.Text, txtTitle.Text))
                Cells(i + 3, v).Resize(3).Value = .Transpose(Array(txtAddress.Text, txtPhone.Text, txtEmail.Text))
            Next i
        Next v
    End With