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]

yes...that's correct. I tried debugging it and find the following: ".Cells(x + 1, i).Value = <application defined or object defined error>"

Here is what I have:
With ws
LastR = .Range("a1").End(xlDown).Row
LastR1 = .Range("a12").End(xlDown).Row
If Me.TextBox1 = "Blue" Then
x = LastR
Else
x = LastR1
End If
For i = 1 To 3
.Cells(x + 1, i).Value = Me.Textbox1
.Cells(x + 1, i).Value = Me.Textbox2
.Cells(x + 1, i).Value = Me.Textbox3
Next
Set ws = Nothing
End With

End Sub

**I know it is probably something simple to fix, but I can't find it.

 

[sulrich]

Jindon...this is the error :
.Cells(x + 1, i).Value ="application defined or object defined error"

 

[jindon]

sulrich,

Ok then try following

1) when you are in VB editor go to View Local Window
2) click somewhere on the code
3) Hit F8, then you will see all the local variables in the local window.
4) Check values of LastR and LastR1

I think one of those two causeing a trouble.

rgds,
jindon

 

[sulrich]

I show for LastR=65536 and LastR1=65536

 

[jindon]

sulrich,

That means you only have data in cell A1, and your second table is empty, is this correct?

rgds,
jindon

 

[sulrich]

That is correct!!

 

[jindon]

OK

change the code

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("a12").End(xlUp).Row
    LastR1 = .Range("a11").End(xlDown).Row
    If LastR = 1 Then
        MsgBox "Named Range Data1 is full": Exit Sub
    End If
    If .Range("a12") = "" Then: LastR1 = 11
    If UCase(Me.TextBox1) = "BLUE" Then
        x = LastR
    Else
        x = LastR1
    End If
    For i = 1 To 3
        .Cells(x + 1, i).Value = Me.Controls(i)
    Next
    Set ws = Nothing
End With
End Sub

rgds,
jindon

 

[jindon]

correction:

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 LastR = 1 Then
        MsgBox "Named Range Data1 is full": Exit Sub
    End If
    If .Range("a12") = "" Then: LastR1 = 11
    If UCase(Me.TextBox1) = "BLUE" Then
        x = LastR
    Else
        x = LastR1
    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]

Thanks Jindon...you're awesome..

Explain one more thing: How do I get the information from the userform inserted as a row within the named range "Data", which "refers to =Sheet1!$A$1:$E$10" . For example, the named range "Data" will now be "refers to =Sheet1!$A$1:$E$11"? Could I just change the code you've given me?

The code I'm using is:

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
Sheets("Sheet1").Range("a1", Range("E10").End(xlUp)).CurrentRegion.Name = "Data"

 

[jindon]

Hi

try the code

Sub c()
If Not Intersect(Range("a65536").End(xlUp), Sheets("sheet1").Range("Data")) Is Nothing Then
    MsgBox "yes"
Else
    MsgBox "no"
End If
End Sub

jindon