Solved: cells from tables sumed + checkboxes

[white_flag]

Hello :)

I have two tables dynamically named (option1, m_option1) I would like to be sum together like this:
cell 1 from table 1(option1) + cell 2 from table 2 (option2) + Formula1 + Formula 2 ..etc

on those table I would like to add an extra control (checkbox 15 and checkbox 13) ..
if the checkboxes are true the values to be summed, if not just the values from witch checkbox is corspunding to be summed:
ex:
if checkbox 13 = True then
cell 1 from table 1(option1) + cell 2 from table 2 (option2-those to become zero) + Formula1 + Formula 2 ..etc

if checkbox 15 = True then
cell 1 from table 1(option1-those to become zero) + cell 2 from table 2 (option2) + Formula1 + Formula 2 ..etc

I would like to think, that some one understand what I try to do and offcorse to help me a bit.

for an better understanding please look in attachment.
thx for your time

[Bob Phillips]

I can even get started with this one. First, I see nothing called option1 and m_option1.

[white_flag]

sorry .the name of the tables are: product1 etc and m_product1 etc

this it is going (this is not entire code, just the piece that am interested):

Set valori = Worksheets("catalog").Range(Me.Controls("ComboBox" & j).Text)
Set coating = Worksheets("catalog").Range("m_" & Me.Controls("ComboBox" & j).Text) ' define the range for the second table
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)
If valori.Cells(n, m).Value = 0 Then
.FormulaR1C1 = Null
Else
If Formula_C = Empty Then Formula_C = 0
If Formula_B = Empty Then Formula_B = 0
If Formula_A = Empty Then Formula_A = 0

.FormulaR1C1 = "=" _
& .FormulaR1C1 & "*" & Formula_A _
& "+" _
& .FormulaR1C1 & "*" & "r" & rTopLeft.Row & "c/1000" & "*" & Formula_C _
& "+" _
& valori.Cells(n, m).Value & "*(1-" & CDbl(Me.Controls("TextBox" & j + 52).Text) & "/100)" & "*(1+" & CDbl(Me.Controls("TextBox" & j + 53).Text) & "/100)" _
& "+" _
& Formula_B _
& "+" & coating.Cells(n, m).Value - here I have an error (because the n, m are different then the first table so he can not find the n,m for this table)
End If
End With
Next m
Next n
End With

[white_flag]

I solve my problem like this: I add an extra condition with "or" and I "wrapped" the values in an checkbox statement (if checkbox = true .. put the values ifnot values = 0)

j=1
Set values1 = Worksheets("catalog").Range(Me.Controls("ComboBox" & j).Text)
Set values2 = Worksheets("catalog").Range("m_" & Me.Controls("ComboBox" & j).Text)
Set values3 = Worksheets("catalog").Range("m_values3")
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 insulation.Rows.Count
For m = 1 To insulation.Columns.Count
If CheckBox15 = True Then
values1 = values1_insulation.Cells(n, m).Value
Else
values1 = 0
End If
If CheckBox13 = True Then
values2 = values2.Cells(n, m).Value
Else
values2 = 0
End If
If CheckBox16 = True Then
values3 = values2.Cells(n, m).Value
Else
values3 = 0
End If

With rTopLeft.Offset(m, n)
If values1.Cells(n, m).Value = 0 Or values2.Cells(n, m).Value = 0 Then
.FormulaR1C1 = Null
Else
.FormulaR1C1 = "=" _
& .FormulaR1C1 & "*" & Formula_A _
& "+" & Formula_B _
& "+" & .FormulaR1C1 & "*" & "r" & rTopLeft.Row & "c/1000" & "*" & Formula_C _
& "+" & values1 & "*(1-" & CDbl(Me.Controls("TextBox" & j + 52).Text) & "/100)" & "*(1+" & CDbl(Me.Controls("TextBox" & j + 53).Text) & "/100)" _
& "+" & values2 _
& "+(" & .FormulaR1C1 & ")*" & formula_cladding _
& "+" & values3
End If
End With
Next m
Next n
End With

Else
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 9
For m = 1 To 20
With rTopLeft.Offset(m, n)
If Me.Controls("TextBox45").Text = Empty Then Me.Controls("TextBox45").Text = 0
If Me.Controls("TextBox46").Text = Empty Then Me.Controls("TextBox46").Text = 0
If Me.Controls("TextBox48").Text = Empty Then Me.Controls("TextBox48").Text = 1
If Me.Controls("ComboBox" & j + 2).Text > "" And Me.Controls("ComboBox" & j + 3).Text > "" Then
.FormulaR1C1 = "=" _
& .FormulaR1C1 & "*" & Formula_A _
& "+" _
& .FormulaR1C1 & "*" & "r" & rTopLeft.Row & "c/1000" & "*" & Formula_C _
& "+" _
& Formula_B _
& "+" _
& "(" & .FormulaR1C1 & ")*index(metaal," & Me.Controls("Combobox3").ListIndex + 1 & "," & Me.Controls("Combobox4").ListIndex + 1 _
& ")*(1+" & CDbl(Me.Controls("TextBox45").Text) & "/100)" _
& "*(1+" & CDbl(Me.Controls("TextBox46").Text) & "/100)" _
& "*" & CDbl(Me.Controls("TextBox48").Text)
Else
.FormulaR1C1 = "=" _
& .FormulaR1C1 & "*" & Formula_A _
& "+" _
& .FormulaR1C1 & "*" & "r" & rTopLeft.Row & "c/1000" & "*" & Formula_C _
& "+" _
& Formula_B
End If
End With
Next m
Next n
End With
End If
'Next
End Sub



have an nice day.
greetings from Belgium