Adding a loop to a recorded macro

[msampson]

Hi all
I consider myself an intermediate Excel user as far as formulas go, but a total rookie with macros and vba so I appreciate any help you can give. I'm using a Mac with OSX and Excel 2004.
I have set up the structure for what I need and then recorded a macro for the first iteration. I am copying a range of 5 cells from one column (starting with B3-7) to another (always H3-7), then sorting them in with some other numbers that are in the same column (H8-1180), grabbing the result of a formula from one cell (K1) and pasting the value of it in a final place (M7 etc). Then I resort the group of cells and erase the 5 that I copied in so that it is ready to repeat.
What I need is the code for a loop that will go down one cell from where it started before and copy the next five cells (four that are the same and one new one), bring them over like before and when I grab the result of the formula I need to paste it one cell lower than I did previously.
I have one cell (E1) that contains the count of the column where I am grabbing the data from so I assume that can be used in the for loop as in for count = 1 to value of E1.

Thanks for any help
Maureen

 

[Cath]

Hi

was just having read through and noticed noone replied to you.

I think you can do what you need by using the relative reference button. When you start to record a macro the little box comes up that has the stop button and another button next to it that looks like a little spreadsheet. If this is unclicked (which it would be normally) you will be in normal mode. Where if you click on A1 within your macro, then it will always go to A1 when you run your macro.

If you click the button you will then be in relative reference mode. (And it will stay in this mode until you click it again). This way, while you are recording, if you use your arrows and shift key to go 'down one cell' then select the next 5 cells, then copy them. In the macro this will look like
ActiveCell.Offset(0, 1).Range("A1:E1").Select
Selection.copy

i.e. this means your macro will go down one cell, and then select the next 5 cells to the right of it. (A1 in relative reference mode just means the cell you are in, not actually A1).

This way you can get your macros to record going to a cell one down from the position it was in before each time.

Don't know if I explained that very well. I hope this helps. Post again if you didn't get it

Cath

 

[tusharm]

I don't understand your last paragraph about count of the column you are getting the data from. Is it possible you meant the rows rather than column?

The general approach to addressing this type of enhancement to code is to modularize the existing code in a subroutine with an argument that drives the pieces that will vary. In your case, clearly the input range varies. Can the output range be inferred from the input range? It would appear to be so but if not, the other variable piece would be the output range.

sub ExistingCode(InRng as range)
    'replace the reference to Range(B3:B7) with InRng. _
    Replace the reference to M7 with InRng.cells(5).offset(0,10) _
    or Range("M" & InRng.cells(5).row) _
    Rest of the code should be OK other than the usual _
    cleanup of macro recorder code.
    end sub
sub ControlLoop
    for i=3 to range("E1").value
        ExistingCode cells(i,2).resize(5,1)
        next i
    end sub

Hi all
I consider myself an intermediate Excel user as far as formulas go, but a total rookie with macros and vba so I appreciate any help you can give. I'm using a Mac with OSX and Excel 2004.
I have set up the structure for what I need and then recorded a macro for the first iteration. I am copying a range of 5 cells from one column (starting with B3-7) to another (always H3-7), then sorting them in with some other numbers that are in the same column (H8-1180), grabbing the result of a formula from one cell (K1) and pasting the value of it in a final place (M7 etc). Then I resort the group of cells and erase the 5 that I copied in so that it is ready to repeat.
What I need is the code for a loop that will go down one cell from where it started before and copy the next five cells (four that are the same and one new one), bring them over like before and when I grab the result of the formula I need to paste it one cell lower than I did previously.
I have one cell (E1) that contains the count of the column where I am grabbing the data from so I assume that can be used in the for loop as in for count = 1 to value of E1.

Thanks for any help
Maureen

 

[msampson]

by the count of the column I meant the number of rows of data in that column (the result of the count formula)
I tried pasting this in but I'm getting an error "Can't execute code in break mode"
I assume that I take the line "next i" and end sub and put them after my code with the rest before?
Sorry, but I am a real rookie at this.

Here is the recorded code...

Sub ranksum()
'
' ranksum Macro
' Macro recorded 12/1/2005 by Maureen
'

'
Range(B3:B7).Select
Selection.Copy
Range("H3").Select
ActiveSheet.Paste
Range("H3:I180").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 1
Range("K1").Select
Selection.Copy
Range("M7").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("H3:I180").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("I3"), Order1:=xlDescending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ActiveWindow.ScrollRow = 1
Range("H3:H7").Select
Selection.ClearContents


End Sub

 

[tusharm]

by the count of the column I meant the number of rows of data in that column (the result of the count formula)
I tried pasting this in but I'm getting an error "Can't execute code in break mode"
I assume that I take the line "next i" and end sub and put them after my code with the rest before?
Sorry, but I am a real rookie at this.
{snip}

No, I meant exactly what I wrote. Leave the code modularized and have the new control subroutine call the existing ranksum routine with the specified changes. See the untested:

Option Explicit

Sub ControlLoop()
    Dim i As Long
    For i = 3 To Range("E1").Value
        RankSum Cells(i, 2).Resize(5, 1)
        Next i
    End Sub
Sub RankSum(InRng As Range)
    InRng.Select
    Selection.Copy
    Range("H3").Select
    ActiveSheet.Paste
    Range("H3:I180").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("H3"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        ActiveWindow.ScrollRow = 1
    Range("K1").Select
    Selection.Copy
    InRng.Cells(5).Offset(0, 11).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("H3:I180").Select
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("I3"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    ActiveWindow.ScrollRow = 1
    Range("H3:H7").Select
    Selection.ClearContents
    End Sub

Note that one can usually remove all the select/activates/scroll operations that the macro recorder generates. In your case, the below *should* be functionally identical to RankSum.

Sub RankSum2(InRng As Range)
    InRng.Copy Range("H3")
    Application.CutCopyMode = False
    Range("H3:I180").Sort Key1:=Range("H3"), Order1:=xlAscending, _
        Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
        ActiveWindow.ScrollRow = 1
    Range("K1").Copy
    InRng.Cells(5).Offset(0, 11).PasteSpecial _
        Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Range("H3:I180").Sort Key1:=Range("I3"), Order1:=xlDescending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Range("H3:H7").ClearContents
    End Sub

Finally, if you post code, please format it for readability. Paste into the box used for posting to the website, then, select the pasted code and click the 'Code' button above the textbox.

 

[msampson]

thanks for helping out.

when I paste exactly that I get a syntax error though. It highlights the first sub line in yellow and the dim line in green

 

[tusharm]

Does the VBE tell you anything more than "syntax error?"

Is there anything else in the module? Try pasting it into a standard module that you've inserted with Insert | Module.

While I cannot test the code I did ensure that it compiled OK before posting it.

thanks for helping out.

when I paste exactly that I get a syntax error though. It highlights the first sub line in yellow and the dim line in green

 

[msampson]

thanks for helping out.

when I paste exactly that I get a syntax error though. It highlights the first sub line in yellow and the dim line in green

 

[msampson]

even when I put it in a new module it says
compile error:
syntax error

Is there a difference in syntax for Macintosh? I am running Excel 2004 on a Macintosh G4.

 

[tusharm]

There are differences between the Wintel and Mac versions but they are subtle, what I would call second level effects. The differences revolve around (a) VB on the Mac is stuck on v5 (the euqivalent of XL97 on Wintel) and (b) hardware / OS specific differences. None of those have anything to do with the code at hand.

Just to verify the code, I checked it with XL97 and it compiled OK.

Try changing the three references to "i" to "Idx". It's clutching at straws but I am hard pressed to understand why it's complaining about the Dim statement.

If that doesn't work and if the workbook doesn't contain proprietary information you can email it to me. I'll see if I can figure out why it won't work.

even when I put it in a new module it says
compile error:
syntax error

Is there a difference in syntax for Macintosh? I am running Excel 2004 on a Macintosh G4.