Simple loop needed to repeat action to multiple columns

[excelotter]

Hello,

I pretty much have no idea what I'm doing in Excel macros, but have been having a go at editing a recorded macro all day without success.

My macro needs 2 loops. The first works fine, selecting a series of workbooks to apply the rest of the macro to. For the second I need a loop which repeats the action for each of 300 odd columns. I first tried to type out the action for each column and the macro became too long to function! Very bad coding practice I know.

Here's the first loop that works fine:

For m = 2 To 42
f$ = Format(m) & ".xlsx"
ChDrive "N:"
ChDir "N:\My Documents\INDICATOR MACROS 666\stage 2 - requirements filtered 666"
Workbooks.Open Filename:=f$
Range("A2:Y2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

ChDir "N:\My Documents\INDICATOR MACROS 666"
Workbooks.Open Filename:="N:\My Documents\INDICATOR MACROS 666\Resource met assessment 666.xlsx"
Range("A3").Select
ActiveSheet.Paste

...............[code here]


Windows(f$).Activate
Range("W2").Select
ActiveSheet.Paste
Range("W1").Select
ActiveCell.FormulaR1C1 = "max"
Range("X1").Select
ActiveCell.FormulaR1C1 = "sum"


ActiveWorkbook.Save
ActiveWindow.Close

ActiveWindow.Close False

Next m
End Sub



Where the [code here] is above, the original recorded macro did this:

Range("W3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("W3:W210000")
Range("W3:W210000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("X3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("X3:X210000")
Range("X3:X210000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("Y3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("Y3:Y210000")
Range("Y3:Y210000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("Z3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("Z3:Z210000")
Range("Z3:Z210000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("AA3").Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("AA3:AA210000")
Range("AA3:AA210000").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


and kept going for lots and lots of columns..........

I want to write a loop so that my macro does not become too long. I want the loop to repeat the code as you can see above, but indexing the columns. Here is my feeble attempt, which doesn't work:



For n = 26 To 364
Cells(3, n).Select
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range(Cells(3, n), Cells(200010, n))
Range(Cells(3, n), Cells(200010, n)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


Next n



Any suggestions would be very much appreciated. It seems like it should be simple enough, but I have very, very little experience of VBA.

Cheers,
Amy

 

[p45cal]

if there's no break in the columns you need to copy down then:

Range("W3:MZ3").AutoFill Destination:=Range("W3:MZ200010"), Type:=xlFillDefault
Range("W3:MZ200010").Value = Range("W3:MZ200010").Value   'converts formulae to values

should do it in one.

 

[excelotter]

Thanks p45cal! This works really well. Does exactly what I want, but only if I run it for 200 rather than 200010 rows.

If I try and run it with 200010 rows the amount of data I'm selecting is too much for my computer/excel (2007) to deal with. I get an error message 'excel cannot complete this task with available resources. Choose less data or close other applications'. I tried breaking it down into chunks of columns, e.g. Range ("W3:AWK200010"), but even this seems too much for it to be able to cope with.

Any suggestions?

 

[p45cal]

I tried breaking it down into chunks of columns, e.g. Range ("W3:AWK200010"), but even this seems too much for it to be able to cope with.

It would be because column AWK is much further to the right than column MZ.

 

[excelotter]

Oh sorry, that was a typo in my response. It should of read "e.g. Range ("W3:AW200010")". And this is how I ran it.

 

[p45cal]

but even this seems too much for it to be able to cope with.

Try:

Sub Macro16()
stepsize = [COLOR=Red]16[/COLOR]
For colm = Columns("W").Column To Columns("MZ").Column Step stepsize
    Set zzz = Intersect(Columns("W:MZ"), Cells(3, colm).Resize(200010, stepsize))
    'Debug.Print zzz.Address, zzz.Columns.Count
    zzz.Rows(1).AutoFill Destination:=zzz, Type:=xlFillDefault 'xlFillValues
    zzz.Value = zzz.Value    'converts formulae to values
Next colm
End Sub

where you can experiment by changing the 16 downwards to find the the number of columns it can cope with. I chose 16 since you said that 27 columns was still too much.
Another thing that may reduce memory load, though I'm not sure, is if you change xlFillDefault to xlFillValues. Then you won't need the line afterwards either.

 

[excelotter]

Hi,

Once again, you have come up with a great solution. Thank you! I'm still hitting problems though. It ran ok with stepsize = 5. It reached column CQ, so was doing pretty well. Then it just stopped and came up with error message 'Run-time error '7' Out of memory'. There is available memory on the computer, so not sure what the problem is.

Thanks again.

 

[p45cal]

Did you try xlFillValues and commenting out (with an apostrophe) the
zzz.Value = zzz.Value 'converts formulae to values
line?

 

[excelotter]

Hi,

Yeah, I did try that, but it didn't seem to make any difference. I've got it running now and it works fine (I'm now using stepsize = 1). It just takes a really, really long time! But, I think that's a problem with the power of my computer rather than the code. I can leave it running overnight.

Thanks so much for all your help, you really have saved me so much time. Learning VBA in a couple of days isn't easy!

Amy