set range with offset

[rki1966]

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.

 

[MrKowz]

Maybe this?

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?

 

[rki1966]

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

 

[MrKowz]

Can you please post your full code?

 

[rki1966]

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

 

[MrKowz]

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.

 

[rki1966]

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.

 

[MrKowz]

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.

 

[rki1966]

The rows and columns look correct.

 

[rki1966]

here is what it should look like.

Excel Workbook
	M	N	O	P	Q	R	S	T
1	% of Eq	28	28.5	29	29.5	30	30.5	31
2	3.50%	850,000	765,000	688,500	619,650	557,685	501,917	451,725
3	3.75%	1,660,000	1,494,000	1,344,600	1,210,140	1,089,126	980,213	882,192
4	4.00%	2,470,000	2,223,000	2,000,700	1,800,630	1,620,567	1,458,510	1,312,659
5	4.25%	3,280,000	2,952,000	2,656,800	2,391,120	2,152,008	1,936,807	1,743,126
6	4.50%	4,090,000	3,681,000	3,312,900	2,981,610	2,683,449	2,415,104	2,173,594
DG-NEW

Excel 2003