please help with syntax in code

  • Thread starter tracks via OfficeKB.com
  • Start date
T

tracks via OfficeKB.com

i am testing the following code and get syntax error at copy line
subtest()
Set rng1=worksheets("daily crane info").Range(I7,AA15:AF15)
Set rng2=worksheets("crane wt summary").cells(rows.count,1).end(xlup)
copy rng1 Destination = rng2.offset(1,0)
end sub

this is my first try at code this involved.

thanks in advance for any assisstance, this sight is great for newbies like
me.
Rick Mason
 
D

Don Guillett

try (untested)
Sub test()
Set rng1 = Worksheets("daily crane info").Range("I7,AA15:AF15")
Set rng2 = Worksheets("crane wt summary").Cells(Rows.Count, 1).End(xlUp)
Copy rng1.Destination = rng2.Offset(1, 0)
End Sub
 
D

Dave Peterson

You could almost (but not quite) do something like this:

Option Explicit
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("daily crane info").Range("I7,AA15:AF15")
Set rng2 = Worksheets("crane wt summary").Cells(Rows.Count, 1).End(xlUp)
rng1.Copy _
Destination:=rng2.Offset(1, 0)
End Sub

But since rng1 consists of multiple areas (I7 and AA15:AF15), this will fail.

Without knowing exactly what you want, maybe something like this will help:

Option Explicit
Sub test()
Dim RngToCopy As Range
Dim DestCell As Range
Dim myArea As Range
Dim iCtr As Long

With Worksheets("daily crane info")
Set RngToCopy = .Range("I7,AA15:AF15")
End With

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

iCtr = 0
For Each myArea In RngToCopy.Areas
myArea.Copy _
Destination:=DestCell.Offset(0, iCtr)
iCtr = iCtr + 1
Next myArea

End Sub

This sample may be more clear:

Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub
 
T

tracks via OfficeKB.com

Don said:
try (untested)
Sub test()
Set rng1 = Worksheets("daily crane info").Range("I7,AA15:AF15")
Set rng2 = Worksheets("crane wt summary").Cells(Rows.Count, 1).End(xlUp)
Copy rng1.Destination = rng2.Offset(1, 0)
End Sub
i am testing the following code and get syntax error at copy line
subtest()
[quoted text clipped - 9 lines]
me.
Rick Mason
hi don
i appreciate the help , i got runtime error 438
object doesn't suppoprt this property or method any other suggestions
 
T

tracks via OfficeKB.com

Dave said:
You could almost (but not quite) do something like this:

Option Explicit
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("daily crane info").Range("I7,AA15:AF15")
Set rng2 = Worksheets("crane wt summary").Cells(Rows.Count, 1).End(xlUp)
rng1.Copy _
Destination:=rng2.Offset(1, 0)
End Sub

But since rng1 consists of multiple areas (I7 and AA15:AF15), this will fail.

Without knowing exactly what you want, maybe something like this will help:

Option Explicit
Sub test()
Dim RngToCopy As Range
Dim DestCell As Range
Dim myArea As Range
Dim iCtr As Long

With Worksheets("daily crane info")
Set RngToCopy = .Range("I7,AA15:AF15")
End With

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

iCtr = 0
For Each myArea In RngToCopy.Areas
myArea.Copy _
Destination:=DestCell.Offset(0, iCtr)
iCtr = iCtr + 1
Next myArea

End Sub

This sample may be more clear:

Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub
HI Dave: I did not follow your first example of code, so I used the second. I got a complier error at destination:= syntax eror , i do not understand looks ok to me . if you have any further suggests iam always here. soory i am so long getting back to you rick mason
i am testing the following code and get syntax error at copy line
subtest()
[quoted text clipped - 11 lines]
 
D

Dave Peterson

It worked ok for me. Did you change the code?

tracks via OfficeKB.com said:
Dave said:
You could almost (but not quite) do something like this:

Option Explicit
Sub test()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Worksheets("daily crane info").Range("I7,AA15:AF15")
Set rng2 = Worksheets("crane wt summary").Cells(Rows.Count, 1).End(xlUp)
rng1.Copy _
Destination:=rng2.Offset(1, 0)
End Sub

But since rng1 consists of multiple areas (I7 and AA15:AF15), this will fail.

Without knowing exactly what you want, maybe something like this will help:

Option Explicit
Sub test()
Dim RngToCopy As Range
Dim DestCell As Range
Dim myArea As Range
Dim iCtr As Long

With Worksheets("daily crane info")
Set RngToCopy = .Range("I7,AA15:AF15")
End With

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

iCtr = 0
For Each myArea In RngToCopy.Areas
myArea.Copy _
Destination:=DestCell.Offset(0, iCtr)
iCtr = iCtr + 1
Next myArea

End Sub

This sample may be more clear:

Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub
HI Dave: I did not follow your first example of code, so I used the second. I got a complier error at destination:= syntax eror , i do not understand looks ok to me . if you have any further suggests iam always here. soory i am so long getting back to you rick mason
i am testing the following code and get syntax error at copy line
subtest()
[quoted text clipped - 11 lines]
 
T

tracks via OfficeKB.com

Dave said:
It worked ok for me. Did you change the code?
[quoted text clipped - 66 lines]
HI, DAVE : I dod not change the code , i will recheck for type-O's and try
again.

thank very much for you help
 
D

Dave Peterson

If you still have trouble, post the your current code.

tracks via OfficeKB.com said:
Dave said:
It worked ok for me. Did you change the code?
You could almost (but not quite) do something like this:
[quoted text clipped - 66 lines]
HI, DAVE : I dod not change the code , i will recheck for type-O's and try
again.

thank very much for you help
 
T

tracks via OfficeKB.com

Dave said:
If you still have trouble, post the your current code.
[quoted text clipped - 12 lines]
this is my code as i entered it, if find any errors please ,explain. i got a pade separation after entering"Option Explicit" is this usual/
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy_
Destination:=DestCell


.Range("AA15:AF15").Copy_
Destination:= DestCell.Offset(0, 1)
End With

End Sub

Rick Mason
 
D

Dave Peterson

Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub

Notice that the underscore character has a space before it (both times).

tracks via OfficeKB.com said:
Dave said:
If you still have trouble, post the your current code.
It worked ok for me. Did you change the code?
[quoted text clipped - 12 lines]
this is my code as i entered it, if find any errors please ,explain. i got a pade separation after entering"Option Explicit" is this usual/
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy_
Destination:=DestCell


.Range("AA15:AF15").Copy_
Destination:= DestCell.Offset(0, 1)
End With

End Sub

Rick Mason
 
C

Chip Pearson

You need to change

Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
' to
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)

Note that "Rows" is plural.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
www.cpearson.com
(email address is on the web site)


tracks via OfficeKB.com said:
Dave said:
If you still have trouble, post the your current code.
It worked ok for me. Did you change the code?
[quoted text clipped - 12 lines]
this is my code as i entered it, if find any errors please ,explain. i got
a pade separation after entering"Option Explicit" is this usual/
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy_
Destination:=DestCell


.Range("AA15:AF15").Copy_
Destination:= DestCell.Offset(0, 1)
End With

End Sub

Rick Mason
 
D

Dave Peterson

Ps. It's sometimes better to copy the code from the response than to type it
manually.

tracks via OfficeKB.com said:
Dave said:
If you still have trouble, post the your current code.
It worked ok for me. Did you change the code?
[quoted text clipped - 12 lines]
this is my code as i entered it, if find any errors please ,explain. i got a pade separation after entering"Option Explicit" is this usual/
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy_
Destination:=DestCell


.Range("AA15:AF15").Copy_
Destination:= DestCell.Offset(0, 1)
End With

End Sub

Rick Mason
 
B

Bob O`Bob

Dave said:
Ps. It's sometimes better to copy the code from the response than to type it
manually.


somehow, the superfluous *and potentially misleading* word "sometimes" was
inserted into your posting. Must have been line noise or something.

It is always and without exception better to copy & paste code into news postings
than to retype it.



Bob
--
 
D

Dave Peterson

_Sometimes_ copying posts from web interfaces can introduce bad characters.
It's not always copy and paste and run. No matter how the code is generated,
there may be corrections to be made.

(But I agree that starting by copying and pasting makes things much easier.)
 
T

tracks via OfficeKB.com

Dave said:
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub

Notice that the underscore character has a space before it (both times).
[quoted text clipped - 28 lines]
thanks, Dave once I corrected the types the code worked fine as required by my first post.
what should have copied only the values in the ranges as there are fomulae
within those cell can I change copy so only values are copied as in excell
Rick Mason
 
D

Dave Peterson

Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy
destcell.pastespecial paste:=xlpastevalues

.Range("AA15:AF15").Copy
DestCell.Offset(0, 1).pastespecial paste:=xlpastevalues
End With

End Sub

tracks via OfficeKB.com said:
Dave said:
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy _
Destination:=DestCell

.Range("AA15:AF15").Copy _
Destination:=DestCell.Offset(0, 1)
End With

End Sub

Notice that the underscore character has a space before it (both times).
If you still have trouble, post the your current code.
[quoted text clipped - 28 lines]
thanks, Dave once I corrected the types the code worked fine as required by my first post.
what should have copied only the values in the ranges as there are fomulae
within those cell can I change copy so only values are copied as in excell
Rick Mason
 
T

tracks via OfficeKB.com

Dave said:
Option Explicit
Sub test()

Dim DestCell As Range

With Worksheets("crane wt summary")
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
End With

With Worksheets("daily crane info")
.Range("I7").Copy
destcell.pastespecial paste:=xlpastevalues

.Range("AA15:AF15").Copy
DestCell.Offset(0, 1).pastespecial paste:=xlpastevalues
End With

End Sub
[quoted text clipped - 30 lines]
thanks , Dave your help is greatly appreciated.

Rick Mason
 
T

tracks via OfficeKB.com

Chip said:
You need to change

Set DestCell = .Cells(.Row.Count, 1).End(xlUp).Offset(1, 0)
' to
Set DestCell = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)

Note that "Rows" is plural.
[quoted text clipped - 25 lines]
Rick Mason
hey!!! nice catch on row to rows. I see everyone helps on this site. Sure
am pleased I came
to this site

your help much appreciated by the newbie

Rick Mason
 

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

Top