| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
set range error in add sheet
Hi All,
I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
You just added a new worksheet, so it is now the activesheet. What are you
trying to find on a new empty sheet? Where is this code located? In a worksheet module? Mike F wrote in message ... Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
On Dec 4, 6:39 am, "Mike Fogleman" wrote:
You just added a new worksheet, so it is now the activesheet. What are you trying to find on a new empty sheet? Where is this code located? In a worksheet module? Mike wrote in message ... Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub what can I say you are right.I never thought in that way.By the way how can i check if the sheet already exist or not?if not exist then create the sheet name bugun? thanks for your reply. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
You can not use Find to locate "". It won't work when done manually so for
the same reason it does not work via a macro. If you are looking for truely blank cells (not formulas returning blank) then you could use xlDown and an offest something like this Range("A1").end(xlDown).offset(1,0).Select or usually better come from the bottom up cells(rows.count, "A").end(xlUp).offset(1,0).select FYI when doing a Find you want to set it to a range object and then check the range object to see if it is nothing. Also you really want to specify all of the optional arguments of the find otherwise XL just uses whatever the last settings where whci can cause a lot of grief. -- HTH... Jim Thomlinson " wrote: Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
On Dec 4, 6:54 am, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote: You can not use Find to locate "". It won't work when done manually so for the same reason it does not work via a macro. If you are looking for truely blank cells (not formulas returning blank) then you could use xlDown and an offest something like this Range("A1").end(xlDown).offset(1,0).Select or usually better come from the bottom up cells(rows.count, "A").end(xlUp).offset(1,0).select FYI when doing a Find you want to set it to a range object and then check the range object to see if it is nothing. Also you really want to specify all of the optional arguments of the find otherwise XL just uses whatever the last settings where whci can cause a lot of grief. -- HTH... Jim Thomlinson " wrote: Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub Thanks a lot for your help.I saved a lot space in the code.By the way why is that better from bottom to up?II tried both of them but only the "cells(rows.count, "A").end(xlUp).offset(1,0).select " worked. Thanks again for the help |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
Sub CreateSheet()
Dim ws as Worksheet Dim Found As Boolean For Each ws in ThisWorkbook.Worksheets If ws.Name = "bugun" Then Found = True Next If Found = True Then 'do nothing Else Worksheets.Add.Name = "bugun" End If End Sub Mike F wrote in message ... On Dec 4, 6:39 am, "Mike Fogleman" wrote: You just added a new worksheet, so it is now the activesheet. What are you trying to find on a new empty sheet? Where is this code located? In a worksheet module? Mike wrote in message ... Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub what can I say you are right.I never thought in that way.By the way how can i check if the sheet already exist or not?if not exist then create the sheet name bugun? thanks for your reply. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
set range error in add sheet
On Dec 5, 6:34 am, "Mike Fogleman" wrote:
Sub CreateSheet() Dim ws as Worksheet Dim Found As Boolean For Each ws in ThisWorkbook.Worksheets If ws.Name = "bugun" Then Found = True Next If Found = True Then 'do nothing Else Worksheets.Add.Name = "bugun" End If End Sub Mike wrote in message ... On Dec 4, 6:39 am, "Mike Fogleman" wrote: You just added a new worksheet, so it is now the activesheet. What are you trying to find on a new empty sheet? Where is this code located? In a worksheet module? Mike wrote in message ... Hi All, I am getting runtime error on below code,anyone can give a hand why that happens?Thanks in advance. Sub sukre() Dim pn Dim rng As Range pn = ComboBox1.Value bugun = Format(Date, "[$-409]d-mmm-yy;@") If CheckBox1.Value = True Then pn = OTB.Value End If Sheets.Add.Name = bugun Set rng = Range("a1:a10000") rng.Find("", rng(rng.Rows.Count)).Select ' HERE IS WHERE I AM HAVING AN ERROR Selection.Value = bugun ActiveCell.Offset(0, 1).Value = pn ActiveCell.Offset(0, 2).Value = PitUF.DTB.Value ActiveCell.Offset(0, 3).Value = PitUF.RTB.Value PitUF.ComboBox1 = "" PitUF.OTB = "" PitUF.DTB = "" PitUF.RTB = "" PitUF.ComboBox1.SetFocus End Sub what can I say you are right.I never thought in that way.By the way how can i check if the sheet already exist or not?if not exist then create the sheet name bugun? thanks for your reply Thanks a lot for your help,that work for me. have a nice day |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Copied sheet causing duplicate range name error (Excel 2010) | Excel Discussion (Misc queries) | |||
| Copying Sheet - subscript out of range error | Excel Programming | |||
| In multiple sheet copy error subscript out of range ?? HELP | Excel Programming | |||
| Excel macro - Range & Sheet name error | Excel Programming | |||
Linear Mode
