Subset of Range

Kaps_mr2 · Mar 8, 2009

I am trying to obtain a subset of a range. The subset will be a single strip. My code is below - which returns a VALUE error.

So if First_Col_No and Second_Col_No have the values 3 and 6, and Row number has the value 2 then the range that should be returned is the second row of Data_Range - between columns 3 and 6 of this range.

Can anbody help ? Thks

Kaps

Code:

Public Function Determine_Range(First_Col_No As Long, Second_Col_no As Long, Row_number As Long, Data_Range As Range) As Range

With Data_Range
    Set Determine_Range = .Cells(.Cells(Row_number, First_Col_No), .Cells(Row_number, Second_Col_no))
End With


End Function

Weaver · Mar 8, 2009

Set Determine_Range = .range(.Ce</pre>

mikerickson · Mar 8, 2009

Code:

Function SubRange (baseRange as Range, topLeftRow as Long, topLeftCol as Long, bottomRightRow as Long, bottomRightCol as Long) As Range

Rem note all arguments are relative to BaseRange

With BaseRange
    Set SubRange = Range(.Cells(topLeftRow, topLeftCol), .Cells(bottomRightRow, bottomRightCol))
End With

End Function

or

Code:

Function singleRowBetweenColumns (baseRange as Range, rowIndex as Long, leftCol as Long, rightCol as Long) As Range

With BaseRange
    Set singleRowBetweenColumns = Range(.Cells(rowIndex, leftCol), .Cells(rowIndex, rightCol))
End With

Rem alternate
'Set singleRowBetweenColumns = subRange(baseRange, rowIndex, leftCol, rowIndex, rightCol)

End Function

Weaver · Mar 8, 2009

or

Code:

Public Function Determine_Range(First_Col_No As Long, Second_Col_no As Long, Row_number As Long, Data_Range As Range) As Range
    With Data_Range
        Set Determine_Range = .Cells(Row_number, First_Col_No).Resize(, Second_Col_no - First_Col_No + 1)
    End With
End Function

Kaps_mr2 · Mar 8, 2009

Nope - I still get value errors and the values of First_Col_No etc are well defined as I have MSG'ed them. Thanks

Kaps

Code:

Public Function Determine_Range(First_Col_No As Long, Second_Col_no As Long, Row_number As Long, Data_Range As Range) As Range

MsgBox First_Col_No & " " & Second_Col_no & " " & Row_number

With Data_Range
    Set Determine_Range = .Range(.Cells(Row_number, First_Col_No), .Cells(Row_number, Second_Col_no))
End With


End Function

mikerickson · Mar 8, 2009

This worked for me.

Code:

Function SubRange(baseRange As Range, _
                                topLeftRow As Double, topLeftCol As Double, _
                                bottomRightRow As Double, bottomRightCol As Double) As Range
    Rem note all arguments are relative to BaseRange
    With baseRange
        Set SubRange = Range(.Cells(topLeftRow, topLeftCol), .Cells(bottomRightRow, bottomRightCol))
    End With
End Function

Function singleRowBetweenColumns(baseRange As Range, _
                                                        rowIndex As Double, _
                                                        leftCol As Double, rightCol As Double) As Range
                                                        
With baseRange
    Set singleRowBetweenColumns = Range(.Cells(rowIndex, leftCol), .Cells(rowIndex, rightCol))
End With
End Function

<table border=1 cellspacing = 0 bgcolor="#ffffff"><tr bgcolor = "#aaaaaa"><td> <td align=center width=77>A<td align=center width=39>B<td align=center width=39>C<td align=center width=47>D<td align=center width=39>E<td align=center width=40>F<td align=center width=40>G<td align=center width=40>H<td align=center width=40>I<td align=center width=40>J<td align=center width=40>K<td align=center width=40>L
<tr><td align=center bgcolor="#aaaaaa">1<td bgcolor="#FFFFFF" > Row num<td bgcolor="#CCFFFF" > 3<td bgcolor="#FFFFFF" > <td bgcolor="#CCFFFF" > 909<td bgcolor="#FFFFFF" > <td bgcolor="#CCFFFF" > 302<td bgcolor="#CCFFFF" > 303<td bgcolor="#CCFFFF" > 304<td bgcolor="#CCFFFF" > #N/A<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">2<td bgcolor="#FFFFFF" > left col<td bgcolor="#CCFFFF" > 2<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">3<td bgcolor="#FFFFFF" > right col<td bgcolor="#CCFFFF" > 4<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">4<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">5<td bgcolor="#FFFFFF" > top row<td bgcolor="#CCFFCC" > 2<td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 16398<td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 203<td bgcolor="#CCFFCC" > 204<td bgcolor="#CCFFCC" > 205<td bgcolor="#CCFFCC" > 206<td bgcolor="#CCFFCC" > 207<td bgcolor="#CCFFCC" > 208<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">6<td bgcolor="#FFFFFF" > left col<td bgcolor="#CCFFCC" > 3<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 303<td bgcolor="#CCFFCC" > 304<td bgcolor="#CCFFCC" > 305<td bgcolor="#CCFFCC" > 306<td bgcolor="#CCFFCC" > 307<td bgcolor="#CCFFCC" > 308<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">7<td bgcolor="#FFFFFF" > bottom row<td bgcolor="#CCFFCC" > 7<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 403<td bgcolor="#CCFFCC" > 404<td bgcolor="#CCFFCC" > 405<td bgcolor="#CCFFCC" > 406<td bgcolor="#CCFFCC" > 407<td bgcolor="#CCFFCC" > 408<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">8<td bgcolor="#FFFFFF" > right col<td bgcolor="#CCFFCC" > 8<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 503<td bgcolor="#CCFFCC" > 504<td bgcolor="#CCFFCC" > 505<td bgcolor="#CCFFCC" > 506<td bgcolor="#CCFFCC" > 507<td bgcolor="#CCFFCC" > 508<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">9<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 603<td bgcolor="#CCFFCC" > 604<td bgcolor="#CCFFCC" > 605<td bgcolor="#CCFFCC" > 606<td bgcolor="#CCFFCC" > 607<td bgcolor="#CCFFCC" > 608<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">10<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#CCFFCC" > 703<td bgcolor="#CCFFCC" > 704<td bgcolor="#CCFFCC" > 705<td bgcolor="#CCFFCC" > 706<td bgcolor="#CCFFCC" > 707<td bgcolor="#CCFFCC" > 708<td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">11<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">12<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">13<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">14<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">15<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > Data<td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > <td bgcolor="#FFFFFF" > </tr>
<tr><td align=center bgcolor="#aaaaaa">16<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 101<td bgcolor="#FFFF99" > 102<td bgcolor="#FFFF99" > 103<td bgcolor="#FFFF99" > 104<td bgcolor="#FFFF99" > 105<td bgcolor="#FFFF99" > 106<td bgcolor="#FFFF99" > 107<td bgcolor="#FFFF99" > 108<td bgcolor="#FFFF99" > 109<td bgcolor="#FFFF99" > 110<td bgcolor="#FFFF99" > 111</tr>
<tr><td align=center bgcolor="#aaaaaa">17<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 201<td bgcolor="#FFFF99" > 202<td bgcolor="#FFFF99" > 203<td bgcolor="#FFFF99" > 204<td bgcolor="#FFFF99" > 205<td bgcolor="#FFFF99" > 206<td bgcolor="#FFFF99" > 207<td bgcolor="#FFFF99" > 208<td bgcolor="#FFFF99" > 209<td bgcolor="#FFFF99" > 210<td bgcolor="#FFFF99" > 211</tr>
<tr><td align=center bgcolor="#aaaaaa">18<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 301<td bgcolor="#FFFF99" > 302<td bgcolor="#FFFF99" > 303<td bgcolor="#FFFF99" > 304<td bgcolor="#FFFF99" > 305<td bgcolor="#FFFF99" > 306<td bgcolor="#FFFF99" > 307<td bgcolor="#FFFF99" > 308<td bgcolor="#FFFF99" > 309<td bgcolor="#FFFF99" > 310<td bgcolor="#FFFF99" > 311</tr>
<tr><td align=center bgcolor="#aaaaaa">19<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 401<td bgcolor="#FFFF99" > 402<td bgcolor="#FFFF99" > 403<td bgcolor="#FFFF99" > 404<td bgcolor="#FFFF99" > 405<td bgcolor="#FFFF99" > 406<td bgcolor="#FFFF99" > 407<td bgcolor="#FFFF99" > 408<td bgcolor="#FFFF99" > 409<td bgcolor="#FFFF99" > 410<td bgcolor="#FFFF99" > 411</tr>
<tr><td align=center bgcolor="#aaaaaa">20<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 501<td bgcolor="#FFFF99" > 502<td bgcolor="#FFFF99" > 503<td bgcolor="#FFFF99" > 504<td bgcolor="#FFFF99" > 505<td bgcolor="#FFFF99" > 506<td bgcolor="#FFFF99" > 507<td bgcolor="#FFFF99" > 508<td bgcolor="#FFFF99" > 509<td bgcolor="#FFFF99" > 510<td bgcolor="#FFFF99" > 511</tr>
<tr><td align=center bgcolor="#aaaaaa">21<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 601<td bgcolor="#FFFF99" > 602<td bgcolor="#FFFF99" > 603<td bgcolor="#FFFF99" > 604<td bgcolor="#FFFF99" > 605<td bgcolor="#FFFF99" > 606<td bgcolor="#FFFF99" > 607<td bgcolor="#FFFF99" > 608<td bgcolor="#FFFF99" > 609<td bgcolor="#FFFF99" > 610<td bgcolor="#FFFF99" > 611</tr>
<tr><td align=center bgcolor="#aaaaaa">22<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 701<td bgcolor="#FFFF99" > 702<td bgcolor="#FFFF99" > 703<td bgcolor="#FFFF99" > 704<td bgcolor="#FFFF99" > 705<td bgcolor="#FFFF99" > 706<td bgcolor="#FFFF99" > 707<td bgcolor="#FFFF99" > 708<td bgcolor="#FFFF99" > 709<td bgcolor="#FFFF99" > 710<td bgcolor="#FFFF99" > 711</tr>
<tr><td align=center bgcolor="#aaaaaa">23<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 801<td bgcolor="#FFFF99" > 802<td bgcolor="#FFFF99" > 803<td bgcolor="#FFFF99" > 804<td bgcolor="#FFFF99" > 805<td bgcolor="#FFFF99" > 806<td bgcolor="#FFFF99" > 807<td bgcolor="#FFFF99" > 808<td bgcolor="#FFFF99" > 809<td bgcolor="#FFFF99" > 810<td bgcolor="#FFFF99" > 811</tr>
<tr><td align=center bgcolor="#aaaaaa">24<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 901<td bgcolor="#FFFF99" > 902<td bgcolor="#FFFF99" > 903<td bgcolor="#FFFF99" > 904<td bgcolor="#FFFF99" > 905<td bgcolor="#FFFF99" > 906<td bgcolor="#FFFF99" > 907<td bgcolor="#FFFF99" > 908<td bgcolor="#FFFF99" > 909<td bgcolor="#FFFF99" > 910<td bgcolor="#FFFF99" > 911</tr>
<tr><td align=center bgcolor="#aaaaaa">25<td bgcolor="#FFFFFF" > <td bgcolor="#FFFF99" > 1001<td bgcolor="#FFFF99" > 1002<td bgcolor="#FFFF99" > 1003<td bgcolor="#FFFF99" > 1004<td bgcolor="#FFFF99" > 1005<td bgcolor="#FFFF99" > 1006<td bgcolor="#FFFF99" > 1007<td bgcolor="#FFFF99" > 1008<td bgcolor="#FFFF99" > 1009<td bgcolor="#FFFF99" > 1010<td bgcolor="#FFFF99" > 1011</tr></table><table border=1 cellspacing = 0 bgcolor="#ddedcc">
<tr><td colspan=3 align="center">Formulas in this range: </tr>
<tr><td align=center>Range with same formula<td align=center>Cell:<td align=center>holds Formula:</tr>
<tr><td><td align=center>D1 <td align = left >{=SUM(singleRowBetweenColumns(B16:L25, B1,B2,B3))}</tr>
<tr><td>F1:I1<td align=center>F1 <td align = left >{=singleRowBetweenColumns($B$16:$L$25, $B$1, $B$2, $B$3)}</tr>
<tr><td><td align=center>D5 <td align = left >{=SUM(SubRange(B16:L25,B5,B6,B7,B8))}</tr>
<tr><td>F5:K10<td align=center>F5 <td align = left >{=SubRange($B$16:$L$25,$B$5,$B$6,$B$7,$B$8)}</tr>
<tr><td colspan=3 align="center">Array formulas are confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac). </tr></table>

Kaps_mr2 · Mar 9, 2009

I am still having problems with this. It retiurns a VALUE error.

I have now posted the calling function as well. I can't see the wood for the trees. Thanks

kaps

Code:

Public Function Number_Of_Occurences_In_Range(Start_Month As Date, End_Month As Date, Search_Term As String, Search_Engine As String, count_if_text As String) As Long

Dim Months_In_Data As Range
Dim List_Of_Search_Terms As Range
Dim Rankings_To_Search As Range
Dim Count_Range As Range
Dim Cell As Variant

Dim Left_Month As Long
Dim Right_Month As Long
Dim Pos_In_List As Long
Dim c As Long
Dim r As Long

Left_Month = 0

'Set up the months data

Set Months_In_Data = Sheets("System Data").Range("Dates")

'Find the left month data

For c = 1 To Months_In_Data.Columns.Count
    Left_Month = Left_Month + 1
    If Months_In_Data(1, c) = Start_Month Then
        Exit For
    End If
Next c

'Find the right month

c = 0

For c = 1 To Months_In_Data.Columns.Count
    Right_Month = Right_Month + 1
    If Months_In_Data(1, c) = Right_Month Then
        Exit For
    End If
Next c



'Set up the search terms

Set List_Of_Search_Terms = Sheets("Google").Range("search_terms")

Pos_In_List = 0

For r = 1 To List_Of_Search_Terms.Rows.Count
    Pos_In_List = Pos_In_List + 1
    If UCase(List_Of_Search_Terms(r, 1)) = UCase(Search_Term) Then
        Exit For
    End If

Next r



'Position of search term in list


'Determine which engine to use


'Yahoo

MsgBox Search_Engine
If UCase(Search_Engine) = "YAHOO" Then
    Set Rankings_To_Search = Sheets("Yahoo").Range("Yahoo_Rankings")
    With Rankings_To_Search
    .Interior.ColorIndex = 2
    End With
End If

'Microsoft


If UCase(Search_Engine) = "MICROSOFT" Then
    Set Rankings_To_Search = Sheets("Yahoo").Range("Microsoft_Rankings")
End If


'Google

If UCase(Search_Engine) = "GOOGLE" Then
    Set Rankings_To_Search = Sheets("Yahoo").Range("Google_Rankings")
End If


Count_Range = Determine_Range(Left_Month, Right_Month, Pos_In_List, Rankings_To_Search)



Number_Of_Occurences_In_Range = 99882


End Function

Public Function Determine_Range(First_Col_No As Long, Second_Col_no As Long, Row_number As Long, Data_Range As Range) As Range

' MsgBox First_Col_No & " " & Second_Col_no & " " & Row_number

With Data_Range
    Set Determine_Range = Range(.Cells(Row_number, First_Col_No), .Cells(Row_number, Second_Col_no))
End With




End Function

mikerickson · Mar 9, 2009

Have you tried changing First_Col and the other Long arguments to Double?

Subset of Range

Kaps_mr2

Well-known Member

Excel Facts

Weaver

Well-known Member

mikerickson

MrExcel MVP

Weaver

Well-known Member

Kaps_mr2

Well-known Member

mikerickson

MrExcel MVP

Kaps_mr2

Well-known Member

mikerickson

MrExcel MVP

Similar threads

Forum statistics

Share this page

Subset of Range

Well-known Member

Excel Facts

Well-known Member

MrExcel MVP

Well-known Member

Well-known Member

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock