Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smaller VBA? Loop or Offset?
I'm using the following code to import values from one worksheet to another...
Private Sub CB1_Click() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") 'From is next 4 columns for each Result plus 1 row Result.Range("C5:F5").Value = From.Range("C3:F3").Value Result.Range("C6:F6").Value = From.Range("G3:J3").Value Result.Range("C7:F7").Value = From.Range("K3:N3").Value Result.Range("C8:F8").Value = From.Range("O3:R3").Value Result.Range("C9:F9").Value = From.Range("S3:V3").Value Result.Range("C10:F10").Value = From.Range("W3:Z3").Value Result.Range("C11:F11").Value = From.Range("AA3:AD3").Value Result.Range("C12:F12").Value = From.Range("AE3:AH3").Value Result.Range("C13:F13").Value = From.Range("AI3:AL3").Value Result.Range("C14:F14").Value = From.Range("AM3:AP3").Value Result.Range("C15:F15").Value = From.Range("AQ3:AT3").Value Result.Range("C16:F16").Value = From.Range("AU3:AX3").Value Result.Range("C17:F17").Value = From.Range("AY3:BB3").Value Result.Range("C18:F18").Value = From.Range("BC3:BF3").Value Result.Range("C19:F19").Value = From.Range("BG3:BJ3").Value Result.Range("C20:F20").Value = From.Range("BK3:BN3").Value Result.Range("C21:F21").Value = From.Range("BO3:BR3").Value End Sub Can this be done with a leaner/smaller sub? Thanks for your help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smaller VBA? Loop or Offset?
The value assigned to J = 3 should be outside the loop as below.....
sub copyem() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") J=3 for i =5 to 21 Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value J=J+4 next i end sub If this post helps click Yes --------------- Jacob Skaria "Don Guillett" wrote: withOUT testing something like sub copyem() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") for i =5 to 21 J=3 Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value J=J+4 next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Qaspec" wrote in message ... I'm using the following code to import values from one worksheet to another... Private Sub CB1_Click() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") 'From is next 4 columns for each Result plus 1 row Result.Range("C5:F5").Value = From.Range("C3:F3").Value Result.Range("C6:F6").Value = From.Range("G3:J3").Value Result.Range("C7:F7").Value = From.Range("K3:N3").Value Result.Range("C8:F8").Value = From.Range("O3:R3").Value Result.Range("C9:F9").Value = From.Range("S3:V3").Value Result.Range("C10:F10").Value = From.Range("W3:Z3").Value Result.Range("C11:F11").Value = From.Range("AA3:AD3").Value Result.Range("C12:F12").Value = From.Range("AE3:AH3").Value Result.Range("C13:F13").Value = From.Range("AI3:AL3").Value Result.Range("C14:F14").Value = From.Range("AM3:AP3").Value Result.Range("C15:F15").Value = From.Range("AQ3:AT3").Value Result.Range("C16:F16").Value = From.Range("AU3:AX3").Value Result.Range("C17:F17").Value = From.Range("AY3:BB3").Value Result.Range("C18:F18").Value = From.Range("BC3:BF3").Value Result.Range("C19:F19").Value = From.Range("BG3:BJ3").Value Result.Range("C20:F20").Value = From.Range("BK3:BN3").Value Result.Range("C21:F21").Value = From.Range("BO3:BR3").Value End Sub Can this be done with a leaner/smaller sub? Thanks for your help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Smaller VBA? Loop or Offset?
Thanks for the catch.
-- Don Guillett Microsoft MVP Excel SalesAid Software "Jacob Skaria" wrote in message ... The value assigned to J = 3 should be outside the loop as below..... sub copyem() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") J=3 for i =5 to 21 Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value J=J+4 next i end sub If this post helps click Yes --------------- Jacob Skaria "Don Guillett" wrote: withOUT testing something like sub copyem() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") for i =5 to 21 J=3 Result.cells(i,"c").resize(,4).Value = From.cells(3,J).resize(,4).Value J=J+4 next i end sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Qaspec" wrote in message ... I'm using the following code to import values from one worksheet to another... Private Sub CB1_Click() Dim From As Worksheet Dim Result As Worksheet Set From = Worksheets("October") Set Result = Worksheets("OctVariance") 'From is next 4 columns for each Result plus 1 row Result.Range("C5:F5").Value = From.Range("C3:F3").Value Result.Range("C6:F6").Value = From.Range("G3:J3").Value Result.Range("C7:F7").Value = From.Range("K3:N3").Value Result.Range("C8:F8").Value = From.Range("O3:R3").Value Result.Range("C9:F9").Value = From.Range("S3:V3").Value Result.Range("C10:F10").Value = From.Range("W3:Z3").Value Result.Range("C11:F11").Value = From.Range("AA3:AD3").Value Result.Range("C12:F12").Value = From.Range("AE3:AH3").Value Result.Range("C13:F13").Value = From.Range("AI3:AL3").Value Result.Range("C14:F14").Value = From.Range("AM3:AP3").Value Result.Range("C15:F15").Value = From.Range("AQ3:AT3").Value Result.Range("C16:F16").Value = From.Range("AU3:AX3").Value Result.Range("C17:F17").Value = From.Range("AY3:BB3").Value Result.Range("C18:F18").Value = From.Range("BC3:BF3").Value Result.Range("C19:F19").Value = From.Range("BG3:BJ3").Value Result.Range("C20:F20").Value = From.Range("BK3:BN3").Value Result.Range("C21:F21").Value = From.Range("BO3:BR3").Value End Sub Can this be done with a leaner/smaller sub? Thanks for your help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Offset in for loop not working | Excel Programming | |||
Find, Copy offset to offset on other sheet, Run-time 1004. | Excel Programming | |||
Loop with activecell.offset | Excel Programming | |||
Need offset range for loop | Excel Programming | |||
Nested Loop Offset Glitch - Object required (Error 424) | Excel Programming |