View Single Post
  #1   Report Post  
Posted to microsoft.public.excel.programming
Rbp9ad[_2_] Rbp9ad[_2_] is offline
external usenet poster
 
Posts: 25
Default named range problem

I am having a unique problem with this code.PLease help.

Private Sub CommandButton1_Click()
Dim rg1 As Range
Dim rg2 As Range
Dim rg3 As Range
Dim rg As Range
Dim lstrow As Range
Dim lstclm As Range


On Error Resume Next
Set rg1 = Range(TextBox1.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstrow = Range("a1500").End(xlUp)
lstrow.Offset(1, 0).Value = TextBox1.Text
lstrow.Offset(1, 0).EntireRow.Select
Selection.Name = TextBox1.Text
End If
On Error Resume Next
Set rg2 = Range("A" & TextBox2.Text)
If Err.Number < 0 Then
On Error GoTo 0
Set lstclm = Range("iv1").End(xlToLeft)
lstclm.Offset(0, 1).Value = TextBox2.Text
lstclm.Offset(0, 1).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text
lstclm.Offset(0, 2).Value = TextBox2.Text & "OT"
lstclm.Offset(0, 2).EntireColumn.Select
Selection.Name = "A" & TextBox2.Text & "OT"
End If

On Error GoTo 0
Set rg1 = Range(TextBox1.Text)
Set rg2 = Range("A" & TextBox2.Text)
Set rg3 = Range("A" & TextBox2.Text & "OT")
Set rg = Intersect(rg1, rg2)
rg.Select
If TextBox3.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox3.Text
End If
Set rg = Intersect(rg1, rg3)
rg.Select
If TextBox4.Text < "" Then
If Selection.Value < "" Then
MsgBox ("Duplicate")
Exit Sub
End If
Selection.Value = TextBox4.Text
End If
TextBox1.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
End Sub

when the value of textbox2 is 052805 then the code fails when rg3 is set. It
does not with other values.