Offset problem
 

After Dave helped me clean up my code last night I thought I was good to go.
/sigh I have a userform which needs to input data into next available blank
row in database. Code is below. The Select Case stuff offsets, but the
other stuff doesn't. What am I doing wrong?

With Worksheets("Database")
Set Rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With Rng
i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is msForms.TextBox Then
Rng(0, i).Value = ctrl.Value
i = i + 1
End If
Next ctrl
Select Case True
Case optA.Value
Rng.Offset(0, 57).Value = "A"
Case optB.Value
Rng.Offset(0, 57).Value = "B"
End Select

End With
End With

Robbyn

Offset problem
 

modify

Rng(0, i).Value = ctrl.Value

to

Rng.offset(0, i).Value = ctrl.Value

HTH

"Robbyn" wrote:

After Dave helped me clean up my code last night I thought I was good to go.
/sigh I have a userform which needs to input data into next available blank
row in database. Code is below. The Select Case stuff offsets, but the
other stuff doesn't. What am I doing wrong?

With Worksheets("Database")
Set Rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With Rng
i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is msForms.TextBox Then
Rng(0, i).Value = ctrl.Value
i = i + 1
End If
Next ctrl
Select Case True
Case optA.Value
Rng.Offset(0, 57).Value = "A"
Case optB.Value
Rng.Offset(0, 57).Value = "B"
End Select

End With
End With

Robbyn

Offset problem
 

Hi Jim,

Your suggestion didn't work (it moved the data over to the next column,
instead of the next row) BUT you gave me an idea and I tried

Rng(1, i).Value = ctrl.Value

and it worked. No idea why, but I'm grateful. Thanks for your quick
response.

"Jim Thomlinson" wrote:

modify

Rng(0, i).Value = ctrl.Value

to

Rng.offset(0, i).Value = ctrl.Value

HTH

"Robbyn" wrote:

After Dave helped me clean up my code last night I thought I was good to go.
/sigh I have a userform which needs to input data into next available blank
row in database. Code is below. The Select Case stuff offsets, but the
other stuff doesn't. What am I doing wrong?

With Worksheets("Database")
Set Rng = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
With Rng
i = 1
For Each ctrl In Me.Controls
If TypeOf ctrl Is msForms.TextBox Then
Rng(0, i).Value = ctrl.Value
i = i + 1
End If
Next ctrl
Select Case True
Case optA.Value
Rng.Offset(0, 57).Value = "A"
Case optB.Value
Rng.Offset(0, 57).Value = "B"
End Select

End With
End With

Robbyn