set range with offset

rki1966

Active Member
Joined
Feb 1, 2004
Messages
351
In my macro I have counter that sets the location of offset by 1 row but want to use the value of colcnt2 as the column number. I do not want to add one to each time.


Here is the row offset which I want to increase by one. This part works.
Set Allocation = Allocation.Offset(rowoffset:=1)

Please let me now if you need more details.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Maybe this?

Code:
Set Allocation = Cells(Allocation.Offset(rowoffset:=1).Row,colcnt2)

However there might be a better way to accomplish this. Could you please post your code as well as where this fits in?
 
Upvote 0
I must be doing something wrong, the offset is working but now it is not copying the data to the cell. it should copy the value of "Results" into the correct cell. any suggestion

Allocation = Results.Value
 
Upvote 0
Can you please post your full code?
 
Upvote 0
Sub Special()


Dim Prices As Range
Dim i As Integer
Dim t As Integer
Dim z As Integer

Dim Target As Range
Dim Results As Range
Dim T_price As Range
Dim Allocation As Range
Dim PctOfEq As Range
Dim colcnt As Integer
Dim column As Integer
Dim col As Integer
Dim rowcnt As Integer
Dim rows As Integer
Dim colcnt2 As Integer
Dim col2 As Integer





Application.Calculation = xlCalculationAutomatic

Sheets("NEW").Select

col = 25
rows = 7
rowcnt = 0
colcnt2 = 1
col2 = 1

Set Prices = Range("Prices")
Set Target = Range("Target")
Set Allocation = Range("Allocation")
Set T_price = Worksheets("NEW").Range("N1")
Set PctOfEq = Range("PCtOfEq")
Set Results = Range("Results")


For i = 1 To col

If PctOfEq = "" Then
rowcnt = 1
Set PctOfEq = Range("PCtOfEq")
Set T_price = T_price.Offset(columnoffset:=colcnt2)
Set Allocation = Range("Allocation")

Set Allocation = Allocation.Offset(columnoffset:=rowcnt)
t = t + 1
rowcnt = rows + 1
z = z + 1
colcnt2 = col2 + 1


Else
Prices = T_price.Value
Target = PctOfEq.Value


Allocation = Results.Value


Set PctOfEq = PctOfEq.Offset(rowoffset:=1)


Set Allocation = Cells(Allocation.Offset(rowoffset:=1).row, colcnt2)
PctOfEq = PctOfEq.Value


i = i + 1
colcnt = column + 1



End If


Next



End Sub
 
Upvote 0
Can you please describe what the code is meant to do? I am trying to pick it apart, but I think an explanation might speed things up a bit. ;)
 
Upvote 0
I have prices on top row and pctofeq down the left columns.

I copy the price in cell N1 and the pctofeq in cell M2 and calculate an allocation then copy the results in the cells where the price and pctofeq meet.

example:

Price = 28
pctofeq = 4%

N2 is where the first price and pctofeq meet. First allocation will go in this cell.

With a price of 28 and pctof eq = 4% the allocation = 2,470,000
(calculated: range = results)

There are 7 prices across the top and 5 pctofeq down the left column, so there will be 35 allocations.

I hope this helps. Thanks for your help.
 
Upvote 0
Would this be a decently accurate representation of your data?

Row 1 = Prices
Column A = pctofeq

The numbers 1-5 in this table are just a simple represenation of Alloc1-Alloc5 in the side table.

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;">23</td><td style="text-align: right;;">24</td><td style="text-align: right;;">25</td><td style="text-align: right;;">26</td><td style="text-align: right;;">27</td><td style="text-align: right;;">28</td><td style="text-align: right;;">29</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Price</td><td style=";">pctofeq</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">1%</td><td style="text-align: right;;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alloc1</td><td style="text-align: right;;">25</td><td style="text-align: right;;">3%</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;">2%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alloc2</td><td style="text-align: right;;">28</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">3%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alloc3</td><td style="text-align: right;;">29</td><td style="text-align: right;;">4%</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;">4%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;">3</td><td style="text-align: right;;"></td><td style=";">Alloc4</td><td style="text-align: right;;">23</td><td style="text-align: right;;">1%</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;">5%</td><td style="text-align: right;;"></td><td style="text-align: right;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Alloc5</td><td style="text-align: right;;">24</td><td style="text-align: right;;">5%</td></tr></tbody></table><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style="background-color: #E0E0F0;text-align: center;color: #161120"><th><b>TeeTime</b></th></tr></td></thead></table><br /><br />

If so, I have an idea on how to fix this.
 
Upvote 0
here is what it should look like.
Excel Workbook
MNOPQRST
1% of Eq2828.52929.53030.531
23.50%850,000765,000688,500619,650557,685501,917451,725
33.75%1,660,0001,494,0001,344,6001,210,1401,089,126980,213882,192
44.00%2,470,0002,223,0002,000,7001,800,6301,620,5671,458,5101,312,659
54.25%3,280,0002,952,0002,656,8002,391,1202,152,0081,936,8071,743,126
64.50%4,090,0003,681,0003,312,9002,981,6102,683,4492,415,1042,173,594
DG-NEW
Excel 2003
 
Upvote 0

Forum statistics

Threads
1,222,241
Messages
6,164,787
Members
451,916
Latest member
Ndk

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top