offset or index in vba

[jetpack]

I have sheet named Data with 4 columns a,b,c,d and n rows. excel 2016

I need to be able to find value every 60 rows and copy to a new sheet name Data60 as follows to end of rows.

A	B	C	D
A1	MAX(B1:B60)	MIN(C1:C60)	D1
A61	MAX(B61:B120)	MIN(C61:C120)	D61
A121	MAX(B121:B180)	MIN(C121:C180)	D121
A181	MAX(B181:B240)	MIN(C181:C240)	D181
A241	MAX(B241:B300)	MIN(C241:C300)	D241

<tbody>
</tbody>

not conversant in vba, all help greatly appreciated.

 

[jetpack]

put in code revision as you said and msgbox returns 100000, no other results, debug highlights the With Sheets(Sheet2) line.

 

[JustynaMK]

Hi jetpack,

Thanks for clarifying. Try using the following code:

Sub Summary()
    Dim lngN                As Long
    Dim lngCount            As Long
    
    lngN = Sheet1.UsedRange.Rows.Count
    
[COLOR=#ff0000]    With Sheets("Sheet2")[/COLOR]
        For lngCount = 1 To lngN
            If lngCount = 1 Then
                .Range("A" & lngCount).Value = Sheet1.Range("A1").Value
            Else
                .Range("A" & lngCount).Value = Sheet1.Range("D" & lngCount - 1).Value
            End If
        Next lngCount
        For lngCount = 1 To lngN / 60
            .Range("B" & lngCount).Value = WorksheetFunction.Max(Sheet1.Range("B" & (lngCount - 1) * 60 + 1 & ":B" & (lngCount - 1) * 60 + 60))
            .Range("C" & lngCount).Value = WorksheetFunction.Min(Sheet1.Range("C" & (lngCount - 1) * 60 + 1 & ":C" & (lngCount - 1) * 60 + 60))
            .Range("D" & lngCount).Value = Sheet1.Range("D" & (lngCount - 1) * 60 + 1).Value
            '.Range("E" & lngCount).Value = "A" & (lngCount - 1) * 60 + 1
            '.Range("F" & lngCount).Value = "B" & (lngCount - 1) * 60 + 1 & ":B" & (lngCount - 1) * 60 + 60
        Next lngCount
    End With
End Sub

As for your Error message, I suspect it's caused by incorrect "With Sheets(Sheet2)" statement. If you want to reference your Sheet name, you need to use double-quote - highlighted in the code above.

 

[jetpack]

sorry mate, seems my "clarification" instead just jumbled everything.

have dummy data in columns A:D incremented by 1 from 1-100k. the code returns

A	B	C	D
1	60	1	1
1	120	61	61
2	180	121	121
3	240	181	181
4	300	241	241

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

what I need it to return is

A	B	C	D
1	60	1	60
60	120	61	120
120	180	121	180
180	240	181	240
240	300	241	300

<colgroup><col width="64" span="4" style="width:48pt"> </colgroup><tbody>
</tbody>

formulae in B(max) & C(min) are spot on. value in D should start at sheet1!D60 and increment by 60. A1 should be sheet1!A1 for first row and afterward match value in previous row D.

hope that actually clarified it. really appreciate your help.

 

[JustynaMK]

Oki doks! Hope I got this right:

Sub Summary()
    Dim lngN                As Long
    Dim lngCount            As Long
    
    lngN = Sheet1.UsedRange.Rows.Count
    
    With Sheets("Sheet2")
        On Error Resume Next
        For lngCount = 1 To lngN / 60
            .Range("B" & lngCount).Value = WorksheetFunction.Max(Sheet1.Range("B" & (lngCount - 1) * 60 + 1 & ":B" & (lngCount - 1) * 60 + 60))
            .Range("C" & lngCount).Value = WorksheetFunction.Min(Sheet1.Range("C" & (lngCount - 1) * 60 + 1 & ":C" & (lngCount - 1) * 60 + 60))
            .Range("D" & lngCount).Value = Sheet1.Range("D" & lngCount * 60).Value
            .Range("A" & lngCount).Value = .Range("D" & lngCount - 1).Value
        Next lngCount
        On Error GoTo 0
        .Range("A1").Value = Sheet1.Range("A1").Value
    End With
End Sub

 

[jetpack]

JustynaMK.

not only is it working perfectly, crikey, it's blazing fast. gud onya, mate.

have a few q's if you have the time to answer, trying to understand the code sose I can maintain it and learn;

how does the code work for the b column?
does moving the code for the a column to after the main evaluating speed up the process?

is it possible for the macro to work on dynamic data?

ya really know your stuff. thanks a mil.

 

[JustynaMK]

Awesome! Glad it worked for you.

Below is a short description of how the code works for column B:

[COLOR=#ff8c00]    'Count the number of Rows in Sheet1 - in your case it's 100,000[/COLOR]
    lngN = Sheet1.UsedRange.Rows.Count
    
    With Sheets("Sheet2")
[COLOR=#ff8c00]        'We're creating "For" loop - for lngCount starting from [B]1[/B] to 100,000 / 60 = [B]1,666[/B] do the following:[/COLOR]
        For lngCount = 1 To lngN / 60
            [COLOR=#ff8c00]'For the first run (lngCount = 1), Sheet2, cell [B]B1 [/B]will equal to Max of Sheet1, cell [B]B1[/B] [(1-1)*60+1 = 1] to cell [B]B60[/B] [(1-1)*60 + 60][/COLOR]
            .Range([B]"B" & lngCount[/B]).Value = WorksheetFunction.Max(Sheet1.Range([B]"B" & (lngCount - 1) * 60 + 1[/B] & ":B" & [B](lngCount - 1) * 60 + 60[/B]))
        Next lngCount
[COLOR=#ff8c00]        'Next lngCount - "For" loop will be repeated 1,665 more times; taking the next lngCount = 2 as an example:
        'Sheet2, cell [B]B2[/B] will equal to Max of Sheet1, cell [B]B61[/B] [(2-1)*60+1] to cell [B]B120[/B] [(2-1)*60+60][/COLOR]

Sorry, it's difficult to explain... but I hope it makes a little bit more sense now!

As for your question if this code will work on dynamic data - the answer is yes, as long as you manually run this macro every time your dataset in Sheet1 change. The code is not automatic so it won't react to your source data changes and it won't update values in Sheet2 "by itself". For that purpose, you would need to create a Change Event or use Excel formulas that you saw in my first post.

Hope it helps!

 

[jetpack]

thanx for the explanation, think I finally got it in this thick noggin'.

 

[JustynaMK]

Awesome good luck & take care!