Named Ranges with Loop and ranges not changing

[planoato]

Hello all, I am new to posting. I have been able to find most of my answers by searching forums, but I can't find an answer to this one. I am trying to create a set of Named Ranges using a Loop and the offset function. My problem is all the Named Ranges I create have the same range at the end of the loop.

Below is a sample of the data I am working with:

<table border="0" cellpadding="0" cellspacing="0" width="442"><colgroup><col style="mso-width-source:userset;mso-width-alt:7533;width:155pt" width="206"> <col style="mso-width-source:userset;mso-width-alt:3254;width:67pt" width="89"> <col style="mso-width-source:userset;mso-width-alt:3035;width:62pt" width="83"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl78" style="height:12.75pt;width:155pt" height="17" width="206">Buyer</td> <td class="xl79" style="border-left:none;width:67pt" width="89">Starting Row</td> <td class="xl79" style="border-left:none;width:62pt" width="83">Ending Row</td> <td class="xl79" style="border-left:none;width:48pt" width="64"># Rows</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name1</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2</td> <td class="xl85" style="border-top:none;border-left:none" align="right">13</td> <td class="xl85" style="border-top:none;border-left:none" align="right">11</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name2</td> <td class="xl85" style="border-top:none;border-left:none" align="right">14</td> <td class="xl85" style="border-top:none;border-left:none" align="right">210</td> <td class="xl85" style="border-top:none;border-left:none" align="right">196</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name3</td> <td class="xl85" style="border-top:none;border-left:none" align="right">211</td> <td class="xl85" style="border-top:none;border-left:none" align="right">567</td> <td class="xl85" style="border-top:none;border-left:none" align="right">356</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name4</td> <td class="xl85" style="border-top:none;border-left:none" align="right">568</td> <td class="xl85" style="border-top:none;border-left:none" align="right">906</td> <td class="xl85" style="border-top:none;border-left:none" align="right">338</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name5</td> <td class="xl85" style="border-top:none;border-left:none" align="right">907</td> <td class="xl85" style="border-top:none;border-left:none" align="right">908</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name6</td> <td class="xl85" style="border-top:none;border-left:none" align="right">909</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1369</td> <td class="xl85" style="border-top:none;border-left:none" align="right">460</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name7</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1370</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1559</td> <td class="xl85" style="border-top:none;border-left:none" align="right">189</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name8</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1560</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1613</td> <td class="xl85" style="border-top:none;border-left:none" align="right">53</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name9</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1614</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1615</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name10</td> <td class="xl85" style="border-top:none;border-left:none" align="right">1616</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2118</td> <td class="xl85" style="border-top:none;border-left:none" align="right">502</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name11</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2119</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2430</td> <td class="xl85" style="border-top:none;border-left:none" align="right">311</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name12</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2431</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2435</td> <td class="xl85" style="border-top:none;border-left:none" align="right">4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name13</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2436</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2924</td> <td class="xl85" style="border-top:none;border-left:none" align="right">488</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl83" style="height:12.75pt;border-top:none" height="17">Name14</td> <td class="xl85" style="border-top:none;border-left:none" align="right">2925</td> <td class="xl85" style="border-top:none;border-left:none" align="right">20000</td> <td class="xl85" style="border-top:none;border-left:none" align="right">17075</td> </tr> </tbody></table>

Where Column H is the row the range needs to start with and Column J is how many rows the range needs to have.

I have used the following code:
Dim X As Integer
Dim Y As Integer


Range("b3").Select
Y = Range("A1").Value - 2 'a check to see how many named ranges I
'need to create

For X = 1 To Y
ActiveWorkbook.Names.Add Name:="Buyer" & X, RefersToR1C1:= _
"=OFFSET(Sheet1!R1C39,Metrics!RC8,0,Metrics!RC10)"
ActiveWorkbook.Names("Buyer" & X).Comment = ""
ActiveCell.Offset(1, 0).Select

Next X

My Range is on Sheet1, but starting rows and # rows are on a Metrics sheet. I can get the macro to create the number of Named Ranges I need, but they all end up with the same offset formula and ranges pull from the same row (same as the active cell). I tried also tried to move the active cell within the loop but then all the named ranges pull from the last active row.

 

[FastExcel]

Its tricky creating relative names: I would used fixed, something like
ActiveWorkbook.Names.Add Name:="Buyer" & X, RefersToR1C1:= _
"=OFFSET(Sheet1!R1C39,Metrics!R" & (X+2) & "C8,0,Metrics!R" & (X+2) & "C10)"

 

[AlphaFrog]

Hi and welcome.

In the named range formulas...

Do you want the result formulas to reference the cells on the Metrics sheet e.g.
=OFFSET(Sheet1!AM1,Metrics!H2,0,Metrics!J2)

Or do you want the formulas to reference just the values from the metrics sheet?
=OFFSET(Sheet1!AM1,2,0,11)

EDIT: after thinking about it, I guess I answered my own question. You wouldn't use the Offset function if you reference the values.

 

[planoato]

FastExcel,

That is exactly what I needed. Thanks for the quick response