Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Almost there with procedure error
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 192
Default Almost there with procedure error
I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line:

startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)

I'm not sure what the problem is. I've tried re-arranging and doing some
different things, but can't figure it out. Any thoughts?

Thanks!

Mind text wrapping:

Option Explicit
Sub Save_Time()

Dim glcount, j, m, n As Integer
Dim startspot, nextspot As Range

j = 0

Set startspot = Range("A1") 'or wherever you start
Set nextspot = Range("A1")

Do
Set startspot = startspot.Offset(j, 0)
If nextspot = "" Then Exit Do
nextspot.Select
ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

Set nextspot = ActiveCell.Offset(4, 0)
nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(m, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 3)

nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(n, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 4)

j = j + Application.Max(n, m)

Loop

End Sub

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 192
Default Almost there with procedure error
In case it helps, the single line database looks like the following:

Date: 123
Name: 456
Shift: 789

Category A
line 1
line 2
line 3
line 4
line 5

Category B
line 1
line 2
line 3
line 4
line 5
line 6

Date: 123
Name: 456
Shift: 789

Category A
line 1
line 2
line 3
line 4
line 5

Category B
line 1
line 2
line 3
line 4
line 5
line 6


"StumpedAgain" wrote:

I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line:

startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)

I'm not sure what the problem is. I've tried re-arranging and doing some
different things, but can't figure it out. Any thoughts?

Thanks!

Mind text wrapping:

Option Explicit
Sub Save_Time()

Dim glcount, j, m, n As Integer
Dim startspot, nextspot As Range

j = 0

Set startspot = Range("A1") 'or wherever you start
Set nextspot = Range("A1")

Do
Set startspot = startspot.Offset(j, 0)
If nextspot = "" Then Exit Do
nextspot.Select
ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

Set nextspot = ActiveCell.Offset(4, 0)
nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(m, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 3)

nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(n, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 4)

j = j + Application.Max(n, m)

Loop

End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Almost there with procedure error
How long is the value in startspot when the code blows up?

What is in that cell?

StumpedAgain wrote:

I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line:

startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)

I'm not sure what the problem is. I've tried re-arranging and doing some
different things, but can't figure it out. Any thoughts?

Thanks!

Mind text wrapping:

Option Explicit
Sub Save_Time()

Dim glcount, j, m, n As Integer
Dim startspot, nextspot As Range

j = 0

Set startspot = Range("A1") 'or wherever you start
Set nextspot = Range("A1")

Do
Set startspot = startspot.Offset(j, 0)
If nextspot = "" Then Exit Do
nextspot.Select
ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

Set nextspot = ActiveCell.Offset(4, 0)
nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(m, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 3)

nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(n, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 4)

j = j + Application.Max(n, m)

Loop

End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 192
Default Almost there with procedure error
The value in startspot is 10 characters long at that point. So I don't think
it's a length error. I took care of those ones. Any other thoughts?

"Dave Peterson" wrote:

How long is the value in startspot when the code blows up?

What is in that cell?

StumpedAgain wrote:

I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line:

startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)

I'm not sure what the problem is. I've tried re-arranging and doing some
different things, but can't figure it out. Any thoughts?

Thanks!

Mind text wrapping:

Option Explicit
Sub Save_Time()

Dim glcount, j, m, n As Integer
Dim startspot, nextspot As Range

j = 0

Set startspot = Range("A1") 'or wherever you start
Set nextspot = Range("A1")

Do
Set startspot = startspot.Offset(j, 0)
If nextspot = "" Then Exit Do
nextspot.Select
ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

Set nextspot = ActiveCell.Offset(4, 0)
nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(m, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 3)

nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(n, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 4)

j = j + Application.Max(n, m)

Loop

End Sub

--

Dave Peterson

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Almost there with procedure error
What error do you get?

StumpedAgain wrote:

The value in startspot is 10 characters long at that point. So I don't think
it's a length error. I took care of those ones. Any other thoughts?

"Dave Peterson" wrote:

How long is the value in startspot when the code blows up?

What is in that cell?

StumpedAgain wrote:

I have the following program to translate a single-column database into a
matrix with five columns. I get an error on the line:

startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)

I'm not sure what the problem is. I've tried re-arranging and doing some
different things, but can't figure it out. Any thoughts?

Thanks!

Mind text wrapping:

Option Explicit
Sub Save_Time()

Dim glcount, j, m, n As Integer
Dim startspot, nextspot As Range

j = 0

Set startspot = Range("A1") 'or wherever you start
Set nextspot = Range("A1")

Do
Set startspot = startspot.Offset(j, 0)
If nextspot = "" Then Exit Do
nextspot.Select
ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

Set nextspot = ActiveCell.Offset(4, 0)
nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
m = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(m, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 3)

nextspot.Select
Set nextspot = ActiveCell.End(xlDown).Offset(2, 0)

With ActiveCell
n = Range(.Offset(0, 0), .End(xlDown)).Rows.Count
End With

ActiveCell.Resize(n, 1).Select
Selection.Cut Destination:=startspot.Offset(0, 4)

j = j + Application.Max(n, m)

Loop

End Sub

--

Dave Peterson


--

Dave Peterson
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 192
Default Almost there with procedure error
I get a run-time error '424': Object required on the line:

ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)

in the following section. If I change the order and put the
ActiveCell.Offset(2,0).cut line down a couple lines, I get the error on the
ActiveCell.Offset(0,0).cut line leading me to believe that I'm somehow
confusing the macro. Also, it works the first time through the loop. This
error occurs the second time through the loop. Ahh!!!

ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) 'error
here
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)


Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Almost there with procedure error
Instead of guessing at what your code does, how about posting some test data
that's in that single column and what you want it to look like when you're done.

I'm confused about the layout that you showed in your follow up post.



StumpedAgain wrote:

I get a run-time error '424': Object required on the line:

ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2)

in the following section. If I change the order and put the
ActiveCell.Offset(2,0).cut line down a couple lines, I get the error on the
ActiveCell.Offset(0,0).cut line leading me to believe that I'm somehow
confusing the macro. Also, it works the first time through the loop. This
error occurs the second time through the loop. Ahh!!!

ActiveCell.Offset(1, 0).Cut Destination:=startspot.Offset(0, 1)
ActiveCell.Offset(0, 0).Cut Destination:=startspot.Offset(0, 0)
ActiveCell.Offset(2, 0).Cut Destination:=startspot.Offset(0, 2) 'error
here
startspot.Offset(1, 1).Value = Right(startspot.Offset(0, 1).Value,
Len(startspot.Value) - 6)
startspot.Offset(1, 0) = Right(startspot.Offset(0, 0).Value,
Len(startspot.Value) - 6)
startspot.Value = Left(startspot.Value, 4)
startspot.Offset(0, 1).Value = Left(startspot.Offset(0, 1).Value, 4)
startspot.Offset(1, 2).Value = Right(startspot.Offset(0, 2).Value,
Len(startspot.Offset(0, 2).Value) - 7)
startspot.Offset(0, 2).Value = Left(startspot.Offset(0, 2).Value, 5)

--

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

Advanced Search
Display Modes
Hybrid Mode Hybrid 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
Procedure too large error phil-rge-ee Excel Programming 6 January 25th 07 03:11 PM
Form Err.Raise error not trapped by entry procedure error handler [email protected] Excel Programming 1 February 8th 06 10:19 AM
VBA Compile error: Procedure too large? Jerry Dyben Excel Discussion (Misc queries) 1 October 31st 05 10:15 PM
Compile error: Procedure too large Susan Hayes Excel Programming 2 May 20th 05 05:01 PM
Error: The procedure number is out of range Van S. Mabrito Excel Programming 1 February 19th 04 11:33 AM


All times are GMT +1. The time now is 09:27 PM.

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