Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Dear All
I am unsure how to proceed with the following challenge. I have a worksheet with 3 columns (see below). For each group of data with the same value in the first colum I need to 'capture' that data set and then run a predefined function on it. I then need to proceed to 'capture' the second group of data and so on. I'm not sure if the data should be read to an array and if so how to capture it all. My initial feeble attempts either lost the 1st or last data set and then I got stuck with the looping. Thanks Matt Col1 Col2 Col3 BH-01 14.8 0.08 BH-01 28.5 0.85 BH-01 42.1 2 BH-01 82.4 19 BH-01 100 38.1 BH-02 14.9 0.08 BH-02 24.2 0.85 BH-02 34.7 2 BH-02 50.7 4.75 BH-03 22.4 0.08 BH-03 31.6 0.15 BH-03 41.2 0.25 BH-03 56.4 0.43 BH-03 81.8 0.85 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Matt,
This should get you started... 'User must select data range first. 'The data range must be sorted. '----------------------------------- Sub CaptureRanges() Dim rngAll As Excel.Range Dim rngCell As Excel.Range Dim rngFirst As Excel.Range Dim rngToUse As Excel.Range Set rngAll = Selection Set rngFirst = rngAll(1) For Each rngCell In rngAll.Columns(1).Cells If rngCell.Value < rngCell(2, 1).Value Then 'The last number "2" specifies the number of columns. Set rngToUse = Range(rngFirst, rngCell).Offset(0, 1).Resize(, 2) 'using MsgBox to confirm correct range returned. 'instead you can do something with data in rngToUse MsgBox rngToUse.Address Set rngFirst = rngCell(2, 1) End If Next 'rngCell Set rngAll = Nothing Set rngCell = Nothing Set rngFirst = Nothing Set rngToUse = Nothing End Sub '------------------------------------ Regards, Jim Cone San Francisco, USA "Matt W" wrote in message oups.com... Dear All I am unsure how to proceed with the following challenge. I have a worksheet with 3 columns (see below). For each group of data with the same value in the first colum I need to 'capture' that data set and then run a predefined function on it. I then need to proceed to 'capture' the second group of data and so on. I'm not sure if the data should be read to an array and if so how to capture it all. My initial feeble attempts either lost the 1st or last data set and then I got stuck with the looping. Thanks Matt Col1 Col2 Col3 BH-01 14.8 0.08 BH-01 28.5 0.85 BH-01 42.1 2 BH-01 82.4 19 BH-01 100 38.1 BH-02 14.9 0.08 BH-02 24.2 0.85 BH-02 34.7 2 BH-02 50.7 4.75 BH-03 22.4 0.08 BH-03 31.6 0.15 BH-03 41.2 0.25 BH-03 56.4 0.43 BH-03 81.8 0.85 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Are you saying that you need to compare col2 to col3 for matching values,
and if they match run a funcion on the values? If so... You can increment through col1. Compare the values of col2 & col3. if col2.Value = col3.Value... Call the function. There is no need to declare a bunch of ranges. All you need to do is compare the values that are already in the cells and make a function call if they match. There is no need to put this information into an array either, unless you are going to use it elsewhere in the program. If you are going to use it elsewhere in the program, you can create a Type containing a variable for each of the 3 values. Then create a dynamic array of that type, and redim preserve it for each match. This will give you all the matches to use elsewhere. Let me know if this is what you are trying to do... Best Regards, Bob "Matt W" wrote in message oups.com... Dear All I am unsure how to proceed with the following challenge. I have a worksheet with 3 columns (see below). For each group of data with the same value in the first colum I need to 'capture' that data set and then run a predefined function on it. I then need to proceed to 'capture' the second group of data and so on. I'm not sure if the data should be read to an array and if so how to capture it all. My initial feeble attempts either lost the 1st or last data set and then I got stuck with the looping. Thanks Matt Col1 Col2 Col3 BH-01 14.8 0.08 BH-01 28.5 0.85 BH-01 42.1 2 BH-01 82.4 19 BH-01 100 38.1 BH-02 14.9 0.08 BH-02 24.2 0.85 BH-02 34.7 2 BH-02 50.7 4.75 BH-03 22.4 0.08 BH-03 31.6 0.15 BH-03 41.2 0.25 BH-03 56.4 0.43 BH-03 81.8 0.85 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Wow!
Thank you all so much for your help. I have a functioning macro as follows, it isn't pretty but it works - pasted below. What I was trying to do was select find the value in column 3 corresponding to a value of 30 in the second column - basically a stepwise linear interpolation. The part that had me stuck was making sure that I was able to capture all data in cols 2 and 3 that had a matching col1. This works but if anyone has comments that would be much appreciated. Thanks all Sub Get_Grain_Size(gsize As Integer, gsizecol As Integer) Dim c As Object Dim rng As Range Dim counter As Integer Dim rowcnt As Integer Dim bnds As Object counter = 1 rowcnt = -1 Range("A2:A10000").Select For Each c In Selection If c.Value = c.Offset(1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 ElseIf c.Value < c.Offset(1, 0).Value And c.Value = c.Offset(-1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 dat = "B" & counter - rowcnt & ":C" & counter Set bnds = Range(dat) D = stepwiseinterp(gsize, bnds, 2) Worksheets("DataReduction").Cells(counter - rowcnt, gsizecol) = D rowcnt = -1 Else counter = counter + 1 End If Next c End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Wow!
Thank you all so much for your help. I have a functioning macro as follows, it isn't pretty but it works - pasted below. What I was trying to do was select find the value in column 3 corresponding to a value of 30 in the second column - basically a stepwise linear interpolation. The part that had me stuck was making sure that I was able to capture all data in cols 2 and 3 that had a matching col1. This works but if anyone has comments that would be much appreciated. Thanks all Sub Get_Grain_Size(gsize As Integer, gsizecol As Integer) Dim c As Object Dim rng As Range Dim counter As Integer Dim rowcnt As Integer Dim bnds As Object counter = 1 rowcnt = -1 Range("A2:A10000").Select For Each c In Selection If c.Value = c.Offset(1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 ElseIf c.Value < c.Offset(1, 0).Value And c.Value = c.Offset(-1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 dat = "B" & counter - rowcnt & ":C" & counter Set bnds = Range(dat) D = stepwiseinterp(gsize, bnds, 2) Worksheets("DataReduction").Cells(counter - rowcnt, gsizecol) = D rowcnt = -1 Else counter = counter + 1 End If Next c End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Matt,
Just some general comments... Use Option Explicit at the top of each module. Declare all of your variables - Option Explicit will help with this. Use a data type declaration of Long for all variables referring to row numbers. Regards, Jim Cone San Francisco, USA "Matt W" wrote in message oups.com... Wow! Thank you all so much for your help. I have a functioning macro as follows, it isn't pretty but it works - pasted below. What I was trying to do was select find the value in column 3 corresponding to a value of 30 in the second column - basically a stepwise linear interpolation. The part that had me stuck was making sure that I was able to capture all data in cols 2 and 3 that had a matching col1. This works but if anyone has comments that would be much appreciated. Thanks all Sub Get_Grain_Size(gsize As Integer, gsizecol As Integer) Dim c As Object Dim rng As Range Dim counter As Integer Dim rowcnt As Integer Dim bnds As Object counter = 1 rowcnt = -1 Range("A2:A10000").Select For Each c In Selection If c.Value = c.Offset(1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 ElseIf c.Value < c.Offset(1, 0).Value And _ c.Value = c.Offset(-1, 0).Value Then counter = counter + 1 rowcnt = rowcnt + 1 dat = "B" & counter - rowcnt & ":C" & counter Set bnds = Range(dat) D = stepwiseinterp(gsize, bnds, 2) Worksheets("DataReduction").Cells(counter - rowcnt, gsizecol) = D rowcnt = -1 Else counter = counter + 1 End If Next c End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array / Looping Question
Thanks Jim
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to write an array to a range without looping | Excel Worksheet Functions | |||
Error after last object in the array when looping For...each | Excel Programming | |||
Looping through Sheets and passing values to an Array | Excel Programming | |||
Looping question | Excel Programming | |||
Looping Question? | Excel Programming |