PDA

View Full Version : Solved: easier way ?



ndendrinos
10-29-2006, 11:28 AM
hello to all,
I need to have 97 cells of my invoice in sequence on Row 50 starting in A
I have an "X" in A49 and the code I'm using works (here with 16 cells only)
Is there a better way to write this?
Many thanks

Sub test()
Dim LastRow As Range
Set LastRow = Sheets("Invoice").Cells(Rows.Count, "A").End(xlUp)

With LastRow
.Offset(1) = Range("I1")
.Offset(1, 1) = Range("I3")
.Offset(1, 2) = Range("C4")
.Offset(1, 3) = Range("C5")
.Offset(1, 4) = Range("C6")
.Offset(1, 5) = Range("C7")
.Offset(1, 6) = Range("C8")
.Offset(1, 7) = Range("C9")
.Offset(1, 8) = Range("C10")
.Offset(1, 9) = Range("C11")
.Offset(1, 10) = Range("H4")
.Offset(1, 11) = Range("H5")
.Offset(1, 12) = Range("H6")
.Offset(1, 13) = Range("H7")
.Offset(1, 14) = Range("H8")
.Offset(1, 15) = Range("H9")

End With

End Sub

johnske
10-29-2006, 01:33 PM
If they're in sequence, one way would be to use a loop e.g. your example above
Sub testrun()

Dim N As Long

With Sheets("Invoice").Range("A" & Rows.Count).End(xlUp)
.Offset(1, 0) = Range("I1")
.Offset(1, 1) = Range("I3")
For N = 2 To 9
.Offset(1, N) = Range("C" & N + 2)
Next
For N = 10 To 15
.Offset(1, N) = Range("H" & N - 1)
Next
End With
End Sub

ndendrinos
10-29-2006, 02:23 PM
thank you Johnske,
maybe I did not explained it well so here is an example of a modified version (Here the cells will be in sequence in Row 38)
Also... I'm now using ... With Row 37 while I s/b using With Row 38 not sure why
I always need the result in Row 38

johnske
10-29-2006, 05:36 PM
You could also do it this way... Sub testrun2()
'
With Sheets("Invoice")
.Range("A38") = .Range("I1")
.Range("B38") = .Range("I3")

.Range("C4:C11").Copy
.Range("C38").PasteSpecial xlPasteValues, Transpose:=True

.Range("H4:H10").Copy
.Range("K38").PasteSpecial xlPasteValues, Transpose:=True
End With
'
End Sub

ndendrinos
10-30-2006, 04:50 AM
Thank you again Johnske
This is easier to work with.