-
Solved: easier way ?
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
-
If they're in sequence, one way would be to use a loop e.g. your example above [VBA]
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
[/VBA]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
-
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
Last edited by ndendrinos; 10-29-2006 at 02:51 PM.
Thank you for your help
-
You could also do it this way... [vba]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[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
-
Thank you again Johnske
This is easier to work with.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules