Inserting data within a named range..HELP

[sulrich]

I'm not an expert in vba in excel, however I would like some help. I have a worksheet with a named range called "Data". And, also on the worksheet I have a command button that initiates a userform. On that userform I have TextBox1.Value and TextBox2.Value...after the OK button is clicked within the userform, it inserts the data from the userform in the last row of that named range. I have part of the code to that adds the data to the worksheet, but the named range area stays the same. How do I get this to work. This is what I have so far:

Sheets("Sheet1").Range("A65536").End(xlUp)(2, 1).Value = TextBox1.Value
Sheets("Sheet1").Range("A65536").End(xlUp)(1, 2).Value = TextBox2.Value
Sheets("Sheet1").Range("A65536").End(xlUp)(1, 3).Value = TextBox3.Value

Thanks in advance...

 

[sulrich]

Hi Jindon....
Can you explain the above code??? How do I use the code to insert into named range "DATA", without losing the format of the range???

 

[jindon]

sulrich,
try

Private Sub CommandButton1_Click()
Dim ws As Worksheet, LastR As Long, LastR1 As Long, x As Long
Set ws = Sheets("sheet1")
With ws
    LastR = .Range("a10").End(xlUp).Row
    LastR1 = .Range("a11").End(xlDown).Row
    If .Range("a12") = "" Then: LastR1 = 11
    If UCase(Me.TextBox1) = "BLUE" Then
        x = LastR
        If Intersect(.Range("a" & x + 1), .Range("Data")) Is Nothing Then
            MsgBox "Sorry, Data is already full": Exit Sub
        End If
    Else
        x = LastR1
            If Intersect(.Range("a" & x + 1), .Range("Data1")) Is Nothing Then
                MsgBox "Sorry, Data1 is already full": Exit Sub
            End If
    End If
    For i = 1 To 3
        .Cells(x + 1, i).Value = Me.Controls(i)
    Next
    Set ws = Nothing
End With
End Sub

jindon

 

[sulrich]

Thank you soooo much, Jindon