|
Shorting code
Is there a way to short this code?
Sub AddingClients() Dim Startcell As Range Dim shtOffset As Integer Set Startcell = shtClient.Range("b2") shtOffset = shtClient.Range("a1").Value Startcell.Offset(shtOffset, 0) = shtInput.Range("c16") Startcell.Offset(shtOffset, 1) = shtInput.Range("c4") Startcell.Offset(shtOffset, 2) = shtInput.Range("c15") Startcell.Offset(shtOffset, 3) = shtInput.Range("c17") Startcell.Offset(shtOffset, 4) = shtInput.Range("c18") Startcell.Offset(shtOffset, 5) = shtInput.Range("c5") Startcell.Offset(shtOffset, 6) = shtInput.Range("c9") Startcell.Offset(shtOffset, 7) = shtInput.Range("c10") Startcell.Offset(shtOffset, 8) = shtInput.Range("c11") Startcell.Offset(shtOffset, 9) = shtInput.Range("c20") Startcell.Offset(shtOffset, 10) = shtInput.Range("c21") Startcell.Offset(shtOffset, 11) = shtInput.Range("c23") Startcell.Offset(shtOffset, 12) = shtInput.Range("c25") Startcell.Offset(shtOffset, 13) = shtInput.Range("c26") Startcell.Offset(shtOffset, 14) = shtInput.Range("c29") Startcell.Offset(shtOffset, 15) = shtInput.Range("c30") Startcell.Offset(shtOffset, 16) = shtInput.Range("c31") Startcell.Offset(shtOffset, 17) = shtInput.Range("c32") End Sub -- Wag more, bark less |
Shorting code
Something like this (untested) should work...
Sub AddingClients() Dim X As Long Dim Startcell As Range Dim shtOffset As Integer Set Startcell = shtClient.Range("b2") shtOffset = shtClient.Range("a1").Value For X = 0 To 17 Startcell.Offset(shtOffset, X) = shtinput.Range(Split( _ "c16,c4,c15,c17,c18,c5,c9,c10,c11,c20,c21,c23, " & _ "c25,c26,c29,c30,c31,c32", ",")(X)).Value Next End Sub Rick "Brad" wrote in message ... Is there a way to short this code? Sub AddingClients() Dim Startcell As Range Dim shtOffset As Integer Set Startcell = shtClient.Range("b2") shtOffset = shtClient.Range("a1").Value Startcell.Offset(shtOffset, 0) = shtInput.Range("c16") Startcell.Offset(shtOffset, 1) = shtInput.Range("c4") Startcell.Offset(shtOffset, 2) = shtInput.Range("c15") Startcell.Offset(shtOffset, 3) = shtInput.Range("c17") Startcell.Offset(shtOffset, 4) = shtInput.Range("c18") Startcell.Offset(shtOffset, 5) = shtInput.Range("c5") Startcell.Offset(shtOffset, 6) = shtInput.Range("c9") Startcell.Offset(shtOffset, 7) = shtInput.Range("c10") Startcell.Offset(shtOffset, 8) = shtInput.Range("c11") Startcell.Offset(shtOffset, 9) = shtInput.Range("c20") Startcell.Offset(shtOffset, 10) = shtInput.Range("c21") Startcell.Offset(shtOffset, 11) = shtInput.Range("c23") Startcell.Offset(shtOffset, 12) = shtInput.Range("c25") Startcell.Offset(shtOffset, 13) = shtInput.Range("c26") Startcell.Offset(shtOffset, 14) = shtInput.Range("c29") Startcell.Offset(shtOffset, 15) = shtInput.Range("c30") Startcell.Offset(shtOffset, 16) = shtInput.Range("c31") Startcell.Offset(shtOffset, 17) = shtInput.Range("c32") End Sub -- Wag more, bark less |
Shorting code
Thank you!
-- Wag more, bark less "Brad" wrote: Is there a way to short this code? Sub AddingClients() Dim Startcell As Range Dim shtOffset As Integer Set Startcell = shtClient.Range("b2") shtOffset = shtClient.Range("a1").Value Startcell.Offset(shtOffset, 0) = shtInput.Range("c16") Startcell.Offset(shtOffset, 1) = shtInput.Range("c4") Startcell.Offset(shtOffset, 2) = shtInput.Range("c15") Startcell.Offset(shtOffset, 3) = shtInput.Range("c17") Startcell.Offset(shtOffset, 4) = shtInput.Range("c18") Startcell.Offset(shtOffset, 5) = shtInput.Range("c5") Startcell.Offset(shtOffset, 6) = shtInput.Range("c9") Startcell.Offset(shtOffset, 7) = shtInput.Range("c10") Startcell.Offset(shtOffset, 8) = shtInput.Range("c11") Startcell.Offset(shtOffset, 9) = shtInput.Range("c20") Startcell.Offset(shtOffset, 10) = shtInput.Range("c21") Startcell.Offset(shtOffset, 11) = shtInput.Range("c23") Startcell.Offset(shtOffset, 12) = shtInput.Range("c25") Startcell.Offset(shtOffset, 13) = shtInput.Range("c26") Startcell.Offset(shtOffset, 14) = shtInput.Range("c29") Startcell.Offset(shtOffset, 15) = shtInput.Range("c30") Startcell.Offset(shtOffset, 16) = shtInput.Range("c31") Startcell.Offset(shtOffset, 17) = shtInput.Range("c32") End Sub -- Wag more, bark less |
Shorting code
Thank you!
-- Wag more, bark less "Don Guillett" wrote: This should work for a list in col E 16 4 15 17 18 5 9 Sub shortenit() For i = 1 To Cells(Rows.Count, "e").End(xlUp).Row Cells(i, "a").Value = Range("c" & Cells(i, "e")) Next i End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Brad" wrote in message ... Is there a way to short this code? Sub AddingClients() Dim Startcell As Range Dim shtOffset As Integer Set Startcell = shtClient.Range("b2") shtOffset = shtClient.Range("a1").Value Startcell.Offset(shtOffset, 0) = shtInput.Range("c16") Startcell.Offset(shtOffset, 1) = shtInput.Range("c4") Startcell.Offset(shtOffset, 2) = shtInput.Range("c15") Startcell.Offset(shtOffset, 3) = shtInput.Range("c17") Startcell.Offset(shtOffset, 4) = shtInput.Range("c18") Startcell.Offset(shtOffset, 5) = shtInput.Range("c5") Startcell.Offset(shtOffset, 6) = shtInput.Range("c9") Startcell.Offset(shtOffset, 7) = shtInput.Range("c10") Startcell.Offset(shtOffset, 8) = shtInput.Range("c11") Startcell.Offset(shtOffset, 9) = shtInput.Range("c20") Startcell.Offset(shtOffset, 10) = shtInput.Range("c21") Startcell.Offset(shtOffset, 11) = shtInput.Range("c23") Startcell.Offset(shtOffset, 12) = shtInput.Range("c25") Startcell.Offset(shtOffset, 13) = shtInput.Range("c26") Startcell.Offset(shtOffset, 14) = shtInput.Range("c29") Startcell.Offset(shtOffset, 15) = shtInput.Range("c30") Startcell.Offset(shtOffset, 16) = shtInput.Range("c31") Startcell.Offset(shtOffset, 17) = shtInput.Range("c32") End Sub -- Wag more, bark less |
| All times are GMT +1. The time now is 05:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com