 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / December 2007set 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 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 > Hi All, > I am getting runtime error on below code,anyone can give a hand why [quoted text clipped - 21 lines] > PitUF.ComboBox1.SetFocus > End Sub > You just added a new worksheet, so it is now the activesheet. What are you > trying to find on a new empty sheet? [quoted text clipped - 28 lines] > > 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. 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 >> You just added a new worksheet, so it is now the activesheet. What are >> you [quoted text clipped - 34 lines] > create the sheet name bugun? > thanks for your reply. > Sub CreateSheet() > Dim ws as Worksheet [quoted text clipped - 52 lines] > > create the sheet name bugun? > > thanks for your reply Thanks a lot for your help,that work for me. have a nice day 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.  SignatureHTH... Jim Thomlinson > Hi All, > I am getting runtime error on below code,anyone can give a hand why [quoted text clipped - 21 lines] > PitUF.ComboBox1.SetFocus > End Sub 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 [quoted text clipped - 42 lines] > > 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 |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.