Send data from userform to named range

[wpryan]

Hi All,

I have a userform that is used to collect something like 75 pieces of data, which are then sent to the main form with code like this:

ws.Cells(lRow, 4).Value = Me.txtRep1
etc.

After the data is sent to the form, I have some more code which then parses it out into 5 individual named ranges for easier import into an Access database. It's working pretty well. So well, in fact, that now Management wants to expand it to include more data points. I am dreading the grief I will go through, because I will have to offset each and every cell value manually in the code. Is there a way to reference the named range in the code, so at least I can keep the destination fields clear in my mind while doing the job?

 

[My Aswer Is This]

Show us some more of your code where you are taking the data from the Userform and placing it in the range you want. If your dealing with a lot of values we should be able to make a loop that would keep you from having to write a lot of code assuming you have some relationship for example textbox1 data goes in cells(1,1) and textbox2 data goes into cells(1,2).

 

[wpryan]

Hello, and thanks for your reply. Here is code for one of the named ranges:

'--------ClinicData--------
ws.Cells(lRow, 1).Value = Format(Me.TxtTxID & (lRow - 2), "0")
ws.Cells(lRow, 2).Value = Me.txtClinic
ws.Cells(lRow, 3).Value = Me.txtSurgeon
ws.Cells(lRow, 4).Value = Me.txtRep1
ws.Cells(lRow, 5).Value = Me.txtRep2


If Me.txtSN > 4 Then
    ws.Cells(lRow, 6).Value = Right(Me.txtSN, 4)
Else: ws.Cells(lRow, 6).Value = Me.txtSN
End If

ws.Cells(lRow, 7).Value = Me.txtSW
ws.Cells(lRow, 8).Value = Me.cboPhaco
ws.Cells(lRow, 9).Value = Format(Me.txtDate, "dd-mmm-yyyy")

where ws is the destination worksheet and lRow is the last row. In this case the named range is "aClinicData" . Maybe you can see my dilemma... If I have to add two items to this, then each and every cell reference after the inserted columns have to be changed by two... I was thinking if the reference could be made to the named range when moving the data it would be much easier to keep straight in my mind.
I have this code for naming the ranges dynamically:

Sub Name_Ranges()


Sheets("Export").Unprotect Password:="CataractData"
ThisWorkbook.Names.Add Name:="aClinicData", _
    RefersTo:=Sheets("Export").Range("A1:I" & Sheets("Export").Range("A65535").End(xlUp).Row)

End Sub

 

[My Aswer Is This]

I think this may beyond my knowledge base. Hopefully someone else here at Mr. Excel will be able to help you.

 

[wpryan]

...cross-posted here Send data from userform to named range.