Solved: Range = index() + textBox.value

[white_flag]

Hello

I like to know if this is possible:
I have some tables(array), from one of the tables, I would like to take the data summed with an number that it is been taked via an text box and to put the result in an another sheet
I try to use Index function but I do not know how can I take the date from rows and columns wen the columns are 1 till 9 and rows 1 till 20. I do not need the match functions to find the exact string

Formula = index(array,row_num,column_num) + text_box.value
drop to sheet1 on specified range

thx

[Bob Phillips]

What part are you needing help with, you seem to have the basics as far as I can see.

[white_flag]

I need a good start or an good approach (I have an lack of ideas)

[white_flag]

I used this solution:

[vba]
Sub Surfaces()
Dim Formula As Double
Dim i As Long
Dim j As Integer
Dim rTopLeft As Range, catalog
Dim Dia(1 To 5) As Double
Dim tk(1 To 4) As Double
Dim X As Variant, Y As Variant, n As Integer, m As Integer

'array dia.
Dia(1) = 21
Dia(2) = 27
Dia(3) = 34
Dia(4) = 42
Dia(5) = 48

'array tk
tk(1) = 25
tk(2) = 30
tk(3) = 40
tk(4) = 50

j = 1
Set catalog = Worksheets("catalog").Range(Me.Controls("ComboBox" & j).Text)
Set rTopLeft = Worksheets("Sheet1").Range("B1")

With rTopLeft
.Offset(0, 1).Resize(1, UBound(tk)).Value = tk
.Offset(1, 0).Resize(UBound(Dia), 1).Value = Application.Transpose(Dia)

For n = 1 To catalog.Rows.Count
For m = 1 To catalog.Columns.Count
'MsgBox .Cells(n, m).Value
With rTopLeft.Offset(1, 1).Resize(UBound(Dia), UBound(tk))
.FormulaR1C1 = catalog.Cells(n, m).Value + Formula
End With

Next m
Next n
End With

End Sub
[/vba]
but the problem is that the values from cells(n, m) are not going correct. so in the end I just have in the table the last value from "index".
look in attachment for a better understanding. thx

[white_flag]

this is the solution:
[VBA]
Sub Surfaces()
Dim Formula As Double
Dim i As Long
Dim rTopLeft As Range, valori
Dim Dia(1 To 20) As Double
Dim tk(1 To 9) As Double
Dim X As Variant, Y As Variant, n As Integer, m As Integer

'array dia.
Dia(1) = 21
Dia(2) = 27
Dia(3) = 34
Dia(4) = 42
Dia(5) = 48
Dia(6) = 60
Dia(7) = 76
Dia(8) = 89
Dia(9) = 114
Dia(10) = 140
Dia(11) = 168
Dia(12) = 219
Dia(13) = 273
Dia(14) = 324
Dia(15) = 356
Dia(16) = 406
Dia(17) = 457
Dia(18) = 508
Dia(19) = 559
Dia(20) = 610

'array tk
tk(1) = 25
tk(2) = 30
tk(3) = 40
tk(4) = 50
tk(5) = 60
tk(6) = 70
tk(7) = 80
tk(8) = 90
tk(9) = 100

For i = 31 To 40
If Me.Controls("CheckBox" & i - 30) = True Then
If Me.Controls("TextBox" & i).Text = "" Then Me.Controls("TextBox" & i).Text = 0
Formula = Formula + CDbl(Me.Controls("TextBox" & i).Text)
End If
Next
Set rTopLeft = Worksheets("Sheet1").Range("B1")
Set valori = Worksheets("catalog_prijs").Range(Me.Controls("ComboBox2").Text)
With rTopLeft
.Offset(0, 1).Resize(1, UBound(tk)).Value = tk
.Offset(1, 0).Resize(UBound(Dia), 1).Value = Application.Transpose(Dia)
With .Offset(1, 1).Resize(UBound(Dia), UBound(tk))
.FormulaR1C1 = "PI()*(2*r" & rTopLeft.Row & "c+rc" & rTopLeft.Column & ")/1000"
End With
For n = 1 To valori.Rows.Count
For m = 1 To valori.Columns.Count
With rTopLeft.Offset(m, n)
.FormulaR1C1 = "=" & .FormulaR1C1 & "+" & valori.Cells(n, m).Value & "+" & Formula
End With
Next m
Next n
End With
End Sub

[/VBA]