set range error in add sheet

B

baha17

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
 
M

Mike Fogleman

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
 
B

baha17

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?

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
Click to expand...
Click to expand...

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.
 
J

Jim Thomlinson

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.
 
B

baha17

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

[email protected] said:
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
Click to expand...
Click to expand...

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
 
M

Mike Fogleman

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
trying to find on a new empty sheet?
Where is this code located? In a worksheet module?

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
Click to expand...
Click to expand...

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.
Click to expand...
 
B

baha17

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


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?

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
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
Thanks a lot for your help,that work for me.
have a nice day
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy filtered data to sheet 2 1
Code does not error but no copy either 4
need to add 24 rows to each service group 5
Vlookup in VBA 3
Error 91: Object Variable Not Set 3
searching for a combobox.value and filling in textboxes from results 3
Run time 9 error for some users but not everyone 10
for every cell search identical in range, not whole column; how? 2

Top