1004 error

[Casey]

Hi,
I posted this question yesterday and got a response, however, I hav
tried all day to get this to work but still get the error 1004. I a
all out of ideas, could use some help.
I just need to be able to set the CopyCol value = to the first empt
Cell to the right of the single Cell named range "StartCell".

Here is the code.

Private Sub cmdEnterSelection_Click()
Dim rng1 As Range
Dim i As Long
Dim j As Long
Dim CopyCol As Long

Set rng1 = Sheets("Takeoff").Range("TakeOffHeaders")
CopyCol = Sheets("Takeoff").Range("StartCell").End(xlToRight).Offset(0
1).Column'<<<<<<<<<<<ERROR HITS HERE
For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With rng1
.Cells(1, CopyCol).Value = ListBox1.List(i, 0)
.Cells(4, CopyCol).Value = ListBox1.List(i, 1)
.Cells(5, CopyCol).Value = ListBox1.List(i, 2)
.Cells(6, CopyCol).Value = ListBox1.List(i, 3)
End With
CopyCol = CopyCol + 1
End If
Next i
OptionButton2.Value = True
End Su

 

[JLatham]

I copied your code and it works as long as there is at least one empty
column, but if there is data all the way across to column IV, then I get the
Error 1004.

Check and see that there is an available empty cell to the right of the
data. You'll also need to watch the increasing value of CopyCol within your
loop, I think.

You could use error trapping before the CopyCol = Sheets... statement to
test for the error and force CopyCol to a predefined maximum value or other
action as appropriate for the situation.

 

[Tom Ogilvy]

I would guess coming from the last column to the left would work

Dim rw as Long, col as Long
With Sheets("Takeoff")
With .Range("StartCell")
rw = .Row
col = .Column
End with
CopyCol = .Cells(rw,256).End(xltoLeft).offset(0,1).Column
if copycol < col then
copycol = col + 1
end if
End With

 

[Casey]

JLatham and Tom,
First thank you both for your time. J thanks for the tip on error
handling and for testing the code. The named range though, starts out
completely empty; so ???
Tom thanks for your input. I always learn something new from your
posts.
As this project had to be ready to review today, I kept at it and
finally took a different approach, that produced the results I was
looking for. This code is behind a Modless UserForm that has 8
cmdbuttons that change the contents of the Listbox. I wanted the end
user to pick from the list, enter it, pick another list via cmdbutton,
pick from that list, enter it after the previous data and so on. I
don't have a clue how efficient my code is compared to Tom's, so I will
try it both ways. Thanks again guys for the help.
Here is my working Code:
Private Sub cmdEnterSelection_Click()
Dim rng1 As Range
Dim rng As Range
Dim i As Long
Dim j As Long
Dim Entries As Long
Dim CopyCol As Long

Set rng1 = Sheets("Takeoff").Range("TakeOffHeaders")
Set rng = Sheets("Takeoff").Range("ScopeNames")
Entries = Excel.WorksheetFunction.CountA(rng)
CopyCol = 1 + Entries


For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With rng1
..Cells(1, CopyCol).Value = ListBox1.List(i, 0)
..Cells(4, CopyCol).Value = ListBox1.List(i, 1)
..Cells(5, CopyCol).Value = ListBox1.List(i, 2)
..Cells(6, CopyCol).Value = ListBox1.List(i, 3)
End With
CopyCol = CopyCol + 1
End If
Next i
OptionButton2.Value = True
End Sub

 

[JLatham]

Casey, I wouldn't worry too much about efficiency of your code - that will
come with time. The key is that you kept at it until it worked, and working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used. This is not documented in the Help on .End [Excel]. You said
your range was empty to begin with, so that explains that. So starting in an
empty cell and planning on using the .End() requires a test for the special
case "is the current cell or range empty".

Glad you worked through it.

 

[Tom Ogilvy]

I've found that the .End() function will return an error if the used range

goes all the way to the edge of a sheet or if you are in an empty cell when
it is used.

It gives an error if you go all the way to the end of the sheet and then you
try to go once cell farther - which is the case here.

End is not affected by the used range and as far as the beginning cell being
emtpy, it is more important what is in the next cell.

--
Regards,
Tom Ogilvy

Casey, I wouldn't worry too much about efficiency of your code - that will
come with time. The key is that you kept at it until it worked, and working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used. This is not documented in the Help on .End [Excel]. You said
your range was empty to begin with, so that explains that. So starting in an
empty cell and planning on using the .End() requires a test for the special
case "is the current cell or range empty".

Glad you worked through it.

 

[Tom Ogilvy]

With regard to used range - I was speaking of the worksheet property
UsedRange - after re-reading I believe you mean that the path to the edge of
the sheet is contiguous with filled cells. So yes, that will allow the End
command to go all the way to the last cell in the range, but that is no
different than if the entire path to the edge is empty (and performs exactly
as it does manually). The problem occurs when the OP then tried to do an
Offset(0,1) from the right edge.

--
Regards,
Tom Ogilvy

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used.

It gives an error if you go all the way to the end of the sheet and then you
try to go once cell farther - which is the case here.

End is not affected by the used range and as far as the beginning cell being
emtpy, it is more important what is in the next cell.

--
Regards,
Tom Ogilvy

Casey, I wouldn't worry too much about efficiency of your code - that will
come with time. The key is that you kept at it until it worked, and working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used. This is not documented in the Help on .End [Excel]. You said
your range was empty to begin with, so that explains that. So starting

in

an
empty cell and planning on using the .End() requires a test for the special
case "is the current cell or range empty".

Glad you worked through it.

------------------------------------------------------------------------

 

[JLatham]

Thanks for that clarification - makes me smarter about it all. I personally
don't use .End that often, usually finding other ways to find my way to the
end of a range at the expense of the speed it works at. Thanks very much.

With regard to used range - I was speaking of the worksheet property
UsedRange - after re-reading I believe you mean that the path to the edge of
the sheet is contiguous with filled cells. So yes, that will allow the End
command to go all the way to the last cell in the range, but that is no
different than if the entire path to the edge is empty (and performs exactly
as it does manually). The problem occurs when the OP then tried to do an
Offset(0,1) from the right edge.

--
Regards,
Tom Ogilvy

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used.

It gives an error if you go all the way to the end of the sheet and then you
try to go once cell farther - which is the case here.

End is not affected by the used range and as far as the beginning cell being
emtpy, it is more important what is in the next cell.

--
Regards,
Tom Ogilvy

Casey, I wouldn't worry too much about efficiency of your code - that will
come with time. The key is that you kept at it until it worked, and working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell when
it is used. This is not documented in the Help on .End [Excel]. You said
your range was empty to begin with, so that explains that. So starting

in

an
empty cell and planning on using the .End() requires a test for the special
case "is the current cell or range empty".

Glad you worked through it.
:


JLatham and Tom,
First thank you both for your time. J thanks for the tip on error
handling and for testing the code. The named range though, starts out
completely empty; so ???
Tom thanks for your input. I always learn something new from your
posts.
As this project had to be ready to review today, I kept at it and
finally took a different approach, that produced the results I was
looking for. This code is behind a Modless UserForm that has 8
cmdbuttons that change the contents of the Listbox. I wanted the end
user to pick from the list, enter it, pick another list via cmdbutton,
pick from that list, enter it after the previous data and so on. I
don't have a clue how efficient my code is compared to Tom's, so I will
try it both ways. Thanks again guys for the help.
Here is my working Code:
Private Sub cmdEnterSelection_Click()
Dim rng1 As Range
Dim rng As Range
Dim i As Long
Dim j As Long
Dim Entries As Long
Dim CopyCol As Long

Set rng1 = Sheets("Takeoff").Range("TakeOffHeaders")
Set rng = Sheets("Takeoff").Range("ScopeNames")
Entries = Excel.WorksheetFunction.CountA(rng)
CopyCol = 1 + Entries


For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With rng1
.Cells(1, CopyCol).Value = ListBox1.List(i, 0)
.Cells(4, CopyCol).Value = ListBox1.List(i, 1)
.Cells(5, CopyCol).Value = ListBox1.List(i, 2)
.Cells(6, CopyCol).Value = ListBox1.List(i, 3)
End With
CopyCol = CopyCol + 1
End If
Next i
OptionButton2.Value = True
End Sub


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=4545
View this thread: http://www.excelforum.com/showthread.php?threadid=539090

 

[Tom Ogilvy]

I find I use it all the time. I would guess that of the posts in this
group that contain code probably >10% use it. Might be a good one to
master.

--
Regards,
Tom Ogilvy

Thanks for that clarification - makes me smarter about it all. I personally
don't use .End that often, usually finding other ways to find my way to the
end of a range at the expense of the speed it works at. Thanks very much.

With regard to used range - I was speaking of the worksheet property
UsedRange - after re-reading I believe you mean that the path to the edge of
the sheet is contiguous with filled cells. So yes, that will allow the End
command to go all the way to the last cell in the range, but that is no
different than if the entire path to the edge is empty (and performs exactly
as it does manually). The problem occurs when the OP then tried to do an
Offset(0,1) from the right edge.

--
Regards,
Tom Ogilvy

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty

cell
when

it is used.

It gives an error if you go all the way to the end of the sheet and

then
you

try to go once cell farther - which is the case here.

End is not affected by the used range and as far as the beginning cell being
emtpy, it is more important what is in the next cell.

--
Regards,
Tom Ogilvy


Casey, I wouldn't worry too much about efficiency of your code -

that
will

come with time. The key is that you kept at it until it worked, and
working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used range
goes all the way to the edge of a sheet or if you are in an empty cell
when
it is used. This is not documented in the Help on .End [Excel].

You
said

your range was empty to begin with, so that explains that. So

starting
in

an
empty cell and planning on using the .End() requires a test for the
special
case "is the current cell or range empty".

Glad you worked through it.
:


JLatham and Tom,
First thank you both for your time. J thanks for the tip on error
handling and for testing the code. The named range though, starts out
completely empty; so ???
Tom thanks for your input. I always learn something new from your
posts.
As this project had to be ready to review today, I kept at it and
finally took a different approach, that produced the results I was
looking for. This code is behind a Modless UserForm that has 8
cmdbuttons that change the contents of the Listbox. I wanted the end
user to pick from the list, enter it, pick another list via cmdbutton,
pick from that list, enter it after the previous data and so on. I
don't have a clue how efficient my code is compared to Tom's, so I will
try it both ways. Thanks again guys for the help.
Here is my working Code:
Private Sub cmdEnterSelection_Click()
Dim rng1 As Range
Dim rng As Range
Dim i As Long
Dim j As Long
Dim Entries As Long
Dim CopyCol As Long

Set rng1 = Sheets("Takeoff").Range("TakeOffHeaders")
Set rng = Sheets("Takeoff").Range("ScopeNames")
Entries = Excel.WorksheetFunction.CountA(rng)
CopyCol = 1 + Entries


For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With rng1
.Cells(1, CopyCol).Value = ListBox1.List(i, 0)
.Cells(4, CopyCol).Value = ListBox1.List(i, 1)
.Cells(5, CopyCol).Value = ListBox1.List(i, 2)
.Cells(6, CopyCol).Value = ListBox1.List(i, 3)
End With
CopyCol = CopyCol + 1
End If
Next i
OptionButton2.Value = True
End Sub


--
Casey

------------------------------------------------------------------------

Casey's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4545
View this thread:
http://www.excelforum.com/showthread.php?threadid=539090

 

[JLatham]

It is without doubt quicker than my antiquated methods, and I will definitely
be trying it out more and more. You're right - finding the end of a range is
an often used function. One thing about this ng is the learning - I've been
programming VBA since the days of Windows 95 and still am learning about it
all.

I find I use it all the time. I would guess that of the posts in this
group that contain code probably >10% use it. Might be a good one to
master.

--
Regards,
Tom Ogilvy

Thanks for that clarification - makes me smarter about it all. I personally
don't use .End that often, usually finding other ways to find my way to the
end of a range at the expense of the speed it works at. Thanks very much.

With regard to used range - I was speaking of the worksheet property
UsedRange - after re-reading I believe you mean that the path to the edge of
the sheet is contiguous with filled cells. So yes, that will allow the End
command to go all the way to the last cell in the range, but that is no
different than if the entire path to the edge is empty (and performs exactly
as it does manually). The problem occurs when the OP then tried to do an
Offset(0,1) from the right edge.

--
Regards,
Tom Ogilvy


I've found that the .End() function will return an error if the used
range
goes all the way to the edge of a sheet or if you are in an empty cell
when
it is used.

It gives an error if you go all the way to the end of the sheet and then
you
try to go once cell farther - which is the case here.

End is not affected by the used range and as far as the beginning cell
being
emtpy, it is more important what is in the next cell.

--
Regards,
Tom Ogilvy


Casey, I wouldn't worry too much about efficiency of your code - that
will
come with time. The key is that you kept at it until it worked, and
working
code is always preferable to (broken) neat looking code.

I've found that the .End() function will return an error if the used
range
goes all the way to the edge of a sheet or if you are in an empty cell
when
it is used. This is not documented in the Help on .End [Excel]. You
said
your range was empty to begin with, so that explains that. So starting
in
an
empty cell and planning on using the .End() requires a test for the
special
case "is the current cell or range empty".

Glad you worked through it.
:


JLatham and Tom,
First thank you both for your time. J thanks for the tip on error
handling and for testing the code. The named range though, starts out
completely empty; so ???
Tom thanks for your input. I always learn something new from your
posts.
As this project had to be ready to review today, I kept at it and
finally took a different approach, that produced the results I was
looking for. This code is behind a Modless UserForm that has 8
cmdbuttons that change the contents of the Listbox. I wanted the end
user to pick from the list, enter it, pick another list via cmdbutton,
pick from that list, enter it after the previous data and so on. I
don't have a clue how efficient my code is compared to Tom's, so I
will
try it both ways. Thanks again guys for the help.
Here is my working Code:
Private Sub cmdEnterSelection_Click()
Dim rng1 As Range
Dim rng As Range
Dim i As Long
Dim j As Long
Dim Entries As Long
Dim CopyCol As Long

Set rng1 = Sheets("Takeoff").Range("TakeOffHeaders")
Set rng = Sheets("Takeoff").Range("ScopeNames")
Entries = Excel.WorksheetFunction.CountA(rng)
CopyCol = 1 + Entries


For i = 0 To Me.ListBox1.ListCount - 1
If Me.ListBox1.Selected(i) Then
With rng1
.Cells(1, CopyCol).Value = ListBox1.List(i, 0)
.Cells(4, CopyCol).Value = ListBox1.List(i, 1)
.Cells(5, CopyCol).Value = ListBox1.List(i, 2)
.Cells(6, CopyCol).Value = ListBox1.List(i, 3)
End With
CopyCol = CopyCol + 1
End If
Next i
OptionButton2.Value = True
End Sub


--
Casey



------------------------------------------------------------------------
Casey's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=4545
View this thread:
http://www.excelforum.com/showthread.php?threadid=539090