Array / Looping Question

[Matt W]

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

 

[Jim Cone]

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

 

[rcalvanese]

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

 

[Tushar Mehta]

Depending on what you are doing with the 'captured' data, consider a
PivotTable with col1 as the page field. XL will break up the data for
you.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Matt W]

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

 

[Matt W]

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

 

[Jim Cone]

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

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

 

[Matt W]

Thanks Jim