VB Code help

[zpierucci]

I am trying to get my info from my text boxes in my user form into my worksheet. I have done this multiple time before and had easy success but now I'm getting problems. I either gut a runtime error or there is no data transferred. This below transfers no data to the worksheet

Private Sub CommandButton1_Click()
Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range


cNum = 4
Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls("Reg" & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next


Reg1.Text = ""
Reg2.Text = ""
Reg3.Text = ""
Reg4.Text = ""

 

[Joe4]

Welcome to the Board!

I am not so sure that you can cycle through all the "Reg" controls like this:

nextrow = Me.Controls([COLOR=#ff0000]"Reg" & X[/COLOR]).Value

What happens if you try hard-coding it, like this?

nextrow = Me.Controls([COLOR=#ff0000]"Reg1"[/COLOR]).Value

If that works, that confirms that you cannot build the control names dynamically like you have tried.
Maybe consider creating an array that holds all four control names, and iterating through that?

 

[Fluff]

Hi & welcome to MrExcel.
There is nothing obviously wrong with the code.
Do you have 4 textboxes named Reg1, Reg2 etc?
What line of code gives the error & what is the error number & message?

 

[Rajneesh Rawat]

Hi,

replace Reg with textbox and try.

 

[zpierucci]

Hi Fluff, Yes all textboxes are named Reg1 - Reg4. In this case I am getting now error at all. There is just no text being transferred from the userform1 to the excel sheet.

 

[Fluff]

If you add the line in blue, what does the msgbox say

Set nextrow = Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
[COLOR=#0000ff]MsgBox nextrow.Address[/COLOR]
For X = 1 To cNum

 

[zpierucci]

Same thing Rahneesh. No error but no data

 

[zpierucci]

$a$15

 

[Fluff]

Is that the first blank row in col A?
Because your data should go into A15:D15 on sheet2

 

[zpierucci]

Got it working! Thanks so much everyone.