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.

 

[JustynaMK]

Hi jetpack,

Do you think you could use Excel formulas instead of VBA?

For example, you can use the following formulas that will "shift" by 60 with each row:
column A -> =INDEX(A:A,(ROW(A1)-1)*60+1)
column B -> =MAX(INDEX(B:B,(ROW(B1)-1)*60+1):INDEX(B:B,(ROW(B1)-1)*60+60))
column C -> =MIN(INDEX(C:C,(ROW(C1)-1)*60+1):INDEX(C:C,(ROW(C1)-1)*60+60))
column D -> =INDEX(D:D,(ROW(D1)-1)*60+1)

 

[jetpack]

thanks so much mate for replying.

the reason for vba is the sheet is about 100k rows. without automating the process, it becomes unmanageable very quickly. is vba something you can help with?

 

[JustynaMK]

Fair point! Might be faster with VBA in such case

Try the following:

Sub Summary()
    Dim lngN                As Long
    Dim lngCount            As Long
    
[COLOR=#ff0000]    lngN = Sheet1.UsedRange.Rows.Count[/COLOR]
    
    With [COLOR=#0000ff]Sheet2[/COLOR]
        For lngCount = 1 To lngN / 60
            .Range("A" & lngCount).Value = Sheet1.Range("A" & (lngCount - 1) * 60 + 1).Value
            .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
[COLOR=#ff8c00]            '.Range("E" & lngCount).Value = "A" & (lngCount - 1) * 60 + 1[/COLOR]
[COLOR=#ff8c00]            '.Range("F" & lngCount).Value = "B" & (lngCount - 1) * 60 + 1 & ":B" & (lngCount - 1) * 60 + 60[/COLOR]
        Next lngCount
    End With
End Sub

I assumed that your dataset is in Sheet1 (that's where I'm checking how many rows are in a used range) and then in Sheet2 you are creating a summary with calculations in columns A-D. You can uncomment the last two parts just to confirm that ranges for which we present data are correct.

Does it help?

 

[Norie]

Can you clarify exactly what you want on the Data60 sheet and where you want it?

 

[jetpack]

thanks for the code.

unfortunately, I get an error on the first line that starts with .Range

 

[jetpack]

the code in the #2 post works great, but I didn't make it clear that starting with row 2, column A should contain the value in column D row 1, row 3 column A would contain the value from column D row 2, etc to end of data.

appreciate your time and help.

 

[JustynaMK]

Hi,
Can you let me know what is the error message?
Also could you confirm if you have Sheet1 and Sheet2 in your file? Maybe try renaming them in the code, e.g. instead of "With Sheet2" use "With Sheets("Your Sheet Name")".
Additionally, add "MsgBox lngN" to a code (just before the whole "With" statement) and let me know if lngN returned any results.

Edit: sorry, just noticed your second post. Let me know if I understood your request correctly.

For Sheet2, column A, you would like to see the same cells as you have in your Sheet1, column D:
cell A1 ='Sheet1'!D1, cell A2 ='Sheet1'!D2, cell A3='Sheet1'!D3, ..., cell A10000 ='Sheet1'!D10000

Then your remaining columns (B, C, D) will be much smaller because they will present data for each 60th row? E.g. column B:
B1 =MAX('Sheet1'!B1:B60), B2 = =MAX('Sheet1'!B61:B120), etc.

Is that correct?

 

[jetpack]

Norie,

thanks for looking in mate and offering your expertise.

I just clarified to JustynaMK the tweak needed for the code using index() that he has generously supplied.

it would be copied to a new sheet starting at A1.

I need a macro that will automate it as this process is repeated at various times during the day. Not manageable to do manually.

thanks again for your help.

 

[jetpack]

thanks for your reply.

sheet2 row1 A1=sheet1!A1 B1=MAX(sheet1!B1:sheet1!B60) C1=MIN(sheet1!C1:sheet1!C60) D1=sheet1!D60

sheet2 row2 A2=sheet2!D1 B2=MAX(sheet1!B61:sheet1!B120) C2=MIN(sheet1!C61:sheet1!C120) D2=sheet1!D120

sheet2 row3 A3=sheet2!D2 B3=MAX(sheet1!B121:sheet1!B180) C3= sheet1!C121:sheet1!C180) D3= sheet1!D180

this pattern will repeat to end of columns. not conversant in vba so if it is easier or more effiecient, values from column A starting in row 2 could reference the same value on sheet1 as
sheet2 D1 etc. either way it is accomplished, starting in row2 column A will always reference the value in column D of the previous row.

does that clarify?

thanks again.