Loops in VBA

surfsoc

New Member
Joined
Sep 28, 2008
Messages
39
Hi,

I have some inefficient code that I know can be turned into a loop. I am not sure how to do this though. This is an example of a few lines of the code:

Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Range("C19").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D8").Select
Application.CutCopyMode = False
Selection.Copy
Range("C20").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

....so on and so forth. The problem is I get to about 1500 lines of this repeated (all the way down to Range("C250~").Select and I receive a compile error saying "procedure too large" (unsurprisingly).

I think this could be turned into a loop formula and then i could set the limit of the copy and paste results manually. And from what i understand, this would only involve very few lines of code (modifications of the code I have).

Can anybody help me with this?

Thanks
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

Wee Kuang

Board Regular
Joined
Sep 15, 2011
Messages
59
Hi there :)

I thought that you can drag and copy all the same value from C8 all the way down to C250? I'm assuming that they are all the same value.

Cheers.
WK
 
Upvote 0

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,367
Office Version
  1. 365
Platform
  1. Windows
Are you just copying the value in D8 to C19,C20,C21...CXXX?

If you are you don't need a loop.
Code:
XXX = No of rows to copy to
 
Range("C19:C" & XXX).Value = Range("D8").Value
 
Upvote 0

Forum statistics

Threads
1,196,013
Messages
6,012,857
Members
441,736
Latest member
Tkpmm

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top