PDA

View Full Version : VB Excel, increase factor in table



ooitzechyi
04-16-2017, 11:54 PM
Hey guys,
I have some data on hand and I would like to use these data to do some simulations.
For eg,
Cell(1,1) = 100

My simulation data:
Cell(1,2): a
Cell(2,2): a*80%
Cell(3,2): a*60%
Cell(4,2): a*40%
Cell(5,2): a*20%

I tried with below codes but I get all as a*0.4


fct = 0.2
For x = 2 To tbl2.Range.Rows.Count
tbl2.Range.Rows(x).Value = tbl.Range.Cells(2, 1).Value * (fct + 0.2)
Next x

mana
04-17-2017, 03:27 AM
tbl2.Range.Rows(x).Value = tbl.Range.Cells(2, 1).Value * (1 - fct * n)
n = n + 1

ooitzechyi
04-17-2017, 09:31 PM
Hi mana,
thanks~ it helps!
Perhaps you could help in below problems.
Lets say
I have Cell(1,1) = Cell(1,2)/60
And I wish to run the formula in other cells with the formula auto change.
Eg: Cell(5,3) = Cell(5,5)/60



For x = 2 To tbl2.Range.Rows.Count
For y = 2 To tbl2.ListColumns.Count

tbl2.Range.Cells(x, y) = ((tbl2.Range.Cells(x, 1) * TBLotSize.Value) / 60) / (((tbl2.Range.Cells(x, 1) * TBLotSize.Value) / 60) + tbl2.Range.Cells(1, y)) * (1 - tbl.Range.Cells(2, 4) - tbl.Range.Cells(2, 5) - tbl.Range.Cells(2, 6))

Next y
Next x

mana
04-18-2017, 04:14 AM
I need your explanation with sample data and expected results.
I don't need your vba code.

ooitzechyi
04-18-2017, 06:53 PM
I have one table (A1:G11).
For each column, data = Row1 x Col1
For eg:
Cell (2,2) = Cell (2,1) * Cell (1,2)
Cell (3,2) = Cell (3,1) * Cell (1,2)
Cell (2,5) = Cell (2,1) * Cell (1,5)

I tried with loop for col but application defined error.

mana
04-19-2017, 02:54 AM
B2 formula:=$A2*B$1


Option Explicit

Sub test()
Dim t As Range

Set t = Range("a1:g11")

Intersect(t, t.Offset(1, 1)).FormulaR1C1 = _
"=rc" & t.Column & "*r" & t.Row & "c"

End Sub