Offset problem

[Guest]

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

 

[Guest]

modify

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

to

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

HTH

 

[Guest]

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.