Shorting code

B

Brad

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
D

Don Guillett

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
 
B

Brad

Thank you!
--
Wag more, bark less


Don Guillett said:
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
(e-mail address removed)
 

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