# Subset of Range

#### Kaps_mr2

##### Well-known Member
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``````

### Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Set Determine_Range = .range(.Ce</pre>

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``````

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``````

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``````

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><b>A</b><td align=center width=39><b>B</b><td align=center width=39><b>C</b><td align=center width=47><b>D</b><td align=center width=39><b>E</b><td align=center width=40><b>F</b><td align=center width=40><b>G</b><td align=center width=40><b>H</b><td align=center width=40><b>I</b><td align=center width=40><b>J</b><td align=center width=40><b>K</b><td align=center width=40><b>L</b>
<tr><td align=center bgcolor="#aaaaaa"><b>1</b><td bgcolor="#FFFFFF" > <FONT color="#000000">Row num</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">909</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">302</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">303</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">304</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">#N/A</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>2</b><td bgcolor="#FFFFFF" > <FONT color="#000000">left col</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>3</b><td bgcolor="#FFFFFF" > <FONT color="#000000">right col</FONT><td bgcolor="#CCFFFF" > <FONT color="#000000">4</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>4</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>5</b><td bgcolor="#FFFFFF" > <FONT color="#000000">top row</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">2</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">16398</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">203</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">204</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">205</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">206</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">207</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">208</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>6</b><td bgcolor="#FFFFFF" > <FONT color="#000000">left col</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">3</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">303</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">304</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">305</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">306</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">307</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">308</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>7</b><td bgcolor="#FFFFFF" > <FONT color="#000000">bottom row</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">7</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">403</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">404</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">405</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">406</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">407</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">408</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>8</b><td bgcolor="#FFFFFF" > <FONT color="#000000">right col</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">8</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">503</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">504</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">505</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">506</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">507</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">508</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>9</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">603</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">604</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">605</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">606</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">607</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">608</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>10</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">703</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">704</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">705</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">706</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">707</FONT><td bgcolor="#CCFFCC" > <FONT color="#000000">708</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>11</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>12</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>13</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>14</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>15</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">Data</FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>16</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">101</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">102</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">103</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">104</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">105</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">106</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">107</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">108</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">109</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">110</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">111</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>17</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">201</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">202</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">203</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">204</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">205</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">206</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">207</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">208</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">209</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">210</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">211</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>18</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">301</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">302</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">303</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">304</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">305</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">306</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">307</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">308</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">309</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">310</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">311</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>19</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">401</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">402</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">403</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">404</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">405</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">406</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">407</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">408</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">409</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">410</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">411</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>20</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">501</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">502</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">503</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">504</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">505</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">506</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">507</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">508</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">509</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">510</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">511</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>21</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">601</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">602</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">603</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">604</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">605</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">606</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">607</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">608</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">609</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">610</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">611</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>22</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">701</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">702</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">703</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">704</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">705</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">706</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">707</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">708</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">709</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">710</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">711</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>23</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">801</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">802</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">803</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">804</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">805</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">806</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">807</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">808</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">809</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">810</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">811</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>24</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">901</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">902</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">903</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">904</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">905</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">906</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">907</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">908</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">909</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">910</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">911</FONT></tr>
<tr><td align=center bgcolor="#aaaaaa"><b>25</b><td bgcolor="#FFFFFF" > <FONT color="#000000"></FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1001</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1002</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1003</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1004</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1005</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1006</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1007</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1008</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1009</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1010</FONT><td bgcolor="#FFFF99" > <FONT color="#000000">1011</FONT></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>

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

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

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``````

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

Replies
3
Views
244
Replies
1
Views
156
Replies
2
Views
202
Replies
1
Views
309
Replies
1
Views
216

1,203,693
Messages
6,056,760
Members
444,889
Latest member
ibbara

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back