Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > named range problem
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
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.


Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default named range problem
R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
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.



Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default named range problem
I did this and it did not work. Also another value gives me the same problem
062505. I am confused on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
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.





Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,441
Default named range problem
Try this shortened version:

Private Sub CommandButton1_Click()
Dim rg3 As Range
Dim Textbox2 As String
Textbox2 = "052805"
Selection.Name = "A" & Textbox2 & "OT"
Set rg3 = Range("A" & Textbox2 & "OT")
MsgBox "The name " & rg3.Name & " is assigned to " & rg3.Address
End Sub

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I did this and it did not work. Also another value gives me the same problem 062505. I am confused
on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
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.







Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 25
Default named range problem
I do not need to know this, the project I was using it for is completed. The
named range was to be made up off the the date in ddmmyy no dashes or
slashes with an A in front and OT in the back. It would work fine for dates
between july and december. Any dates from the first half of the year did not
work. I made a workaround by just entering dates before july with the first
two letters of the month. I am just trying to understand why this happened?
Why would only half the values work? In excel I can apply these names but
not from the code.

"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
Try this shortened version:

Private Sub CommandButton1_Click()
Dim rg3 As Range
Dim Textbox2 As String
Textbox2 = "052805"
Selection.Name = "A" & Textbox2 & "OT"
Set rg3 = Range("A" & Textbox2 & "OT")
MsgBox "The name " & rg3.Name & " is assigned to " & rg3.Address
End Sub

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
I did this and it did not work. Also another value gives me the same
problem 062505. I am confused on this one.
"Bernie Deitrick" <deitbe @ consumer dot org wrote in message
...
R,

Have you set the TakeFocusOnClick property of the commandbutton to
False?

HTH,
Bernie
MS Excel MVP


"Rbp9ad" wrote in message
...
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.









Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Find in Named Range problem (2nd Try) G.R. New Users to Excel 2 February 27th 08 08:17 PM
Series Named Range Problem Arturo Charts and Charting in Excel 1 July 3rd 07 09:31 PM
external named range problem JKC Excel Discussion (Misc queries) 0 March 7th 06 05:23 PM
Insert named range problem FinChase Excel Programming 11 November 15th 04 07:21 PM
Problem selecting a named range kkknie[_180_] Excel Programming 0 August 11th 04 08:30 PM


All times are GMT +1. The time now is 08:45 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement