a better approach

[white_flag]

Hello I have the following code

based on the drop list (kg/liters/meters) the code will put some formula. But in my case the case "kg" has more option for example if I choose "kg" and in C4=0, D4>0, E=0 will be one formula, if in C4>0, D4=0, E=0 another formula ..etc

But in the end the VBA formula for "kg" will be too big



Sub Test()
Dim c1 As Range

For Each c1 In Range("$J$1:$J" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "kg": Cells(c1.Row, "L") = "=IF(AND(" & Cells(c1.Row, "J").Address & "=" & """kg""" & "," & Cells(c1.Row, "C").Address & ">0)," & """formula1""" & "," & """formula2""" & ")" 'IF(AND(J4="kg";C4>0);"formula1";"formula2")
Case "liters": Cells(c1.Row, "L") = Cells(c1.Row, "C")
'etc
Case Else:
End Select
Next
End Sub


So my question is can be done this in a different way?

[snb]

Please post a sample workbook.
I think in this case it's easier to copy a formula then to 'build' one in VBA.

[white_flag]

ok then (thx!)... I will putted like this (I need row reference for each formula):



Sub Test()
Dim c1 As Range

For Each c1 In Range("$J$1:$J" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "kg": Cells(c1.Row, "L") = "=IF(AND(J" & c1.Row & "=""kg"",C" & c1.Row & ">0),""formula1"",""formula2"")"
Case "liters": Cells(c1.Row, "L") = Cells(c1.Row, "C")
'etc
Case Else:
End Select
Next
End Sub



can be done more simple?

[snb]

Ja, door je Excelbestand hier te plaatsen (zie vorige bericht).
See my first request in my previous post.

[white_flag]

ok, I try to put more conditions via if, else ... but something is not going. See attachment.
the attachment is not working. I will try via Internet explorer

[white_flag]

10475

[snb]

I don't see any real formulae.

You could use:

=IF(AND(C5*D5*E5*F5>0;G5=0);IF(J5="kg";"formule1";"formula2");"")

[white_flag]

thx but I need like 5 formula ..and If I am doing this on excel (I am receiving ...too many parameters)

but What I do not understand is, why the code all the time (based on the logic function) take "formula 2"



Sub Test()
Dim c1 As Range

For Each c1 In Range("$J$2:$J" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "kg"
If (Cells(c1.Row, "C") > 0 & Cells(c1.Row, "D") > 0 & Cells(c1.Row, "E") > 0 & Cells(c1.Row, "F") > 0) And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 1"
ElseIf (Cells(c1.Row, "C") > 0 & Cells(c1.Row, "D") > 0 & Cells(c1.Row, "E") = 0 & Cells(c1.Row, "F") = 0) And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 2"
ElseIf (Cells(c1.Row, "C") > 0 & Cells(c1.Row, "D") > 0 & Cells(c1.Row, "E") > 0 & Cells(c1.Row, "F") = 0) And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 3"
ElseIf (Cells(c1.Row, "C") > 0 & Cells(c1.Row, "D") > 0 & Cells(c1.Row, "E") > 0 & Cells(c1.Row, "F") > 0) And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 4"
ElseIf (Cells(c1.Row, "C") > 0 & Cells(c1.Row, "D") = 0 & Cells(c1.Row, "E") = 0 & Cells(c1.Row, "F") = 0) And Cells(c1.Row, "G") > 0 Then
Cells(c1.Row, "L") = "formula 4"
Else
Cells(c1.Row, "L") = "error"
'...etc
End If
Case "liters": Cells(c1.Row, "L") = Cells(c1.Row, "C")
'etc
Case Else: 'MsgBox "lala"
End Select
Next
End Sub

[white_flag]

I put AND and not &


Sub Test()
Dim c1 As Range

For Each c1 In Range("$J$2:$J" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "kg"
If Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") > 0 And Cells(c1.Row, "E") > 0 And Cells(c1.Row, "F") > 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 1"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") > 0 And Cells(c1.Row, "E") > 0 And Cells(c1.Row, "F") = 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 2"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") = 0 And Cells(c1.Row, "E") > 0 And Cells(c1.Row, "F") = 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 2a"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") = 0 And Cells(c1.Row, "E") = 0 And Cells(c1.Row, "F") > 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 2b"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") > 0 And Cells(c1.Row, "E") = 0 And Cells(c1.Row, "F") = 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 3"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") = 0 And Cells(c1.Row, "E") = 0 And Cells(c1.Row, "F") = 0 And Cells(c1.Row, "G") = 0 Then
Cells(c1.Row, "L") = "formula 4"
ElseIf Cells(c1.Row, "C") > 0 And Cells(c1.Row, "D") = 0 And Cells(c1.Row, "E") = 0 And Cells(c1.Row, "F") = 0 And Cells(c1.Row, "G") > 0 Then
Cells(c1.Row, "L") = "formula 5"
Else
Cells(c1.Row, "L") = "error"
'...etc
End If
Case "liters": Cells(c1.Row, "L") = Cells(c1.Row, "C")
'etc
Case Else: 'MsgBox "lala"
End Select
Next
End Sub


so it is ok for me... snb thx for the support (see U around)

[SamT]

A better approach

This appears to be a sales aid, I think you should consider a Form based, list driven system. I have designed just such a system with 5 major categories, over 700 categories and possibly 12,000 items.

The specific numbers of items in any category was dynamic as were the all the categories and their depths.

I used a custom Font Name to identify Lists, and as each category was selected from Form dropdowns, it populated the next dropdown with the list for the selected subsidiary category.

With five clicks, the system selected the specific product and entered the unit sales and pricing, another few entries completed the order form.

I am attaching a very crude example of the types of lists I used, based on your example. The advantages of this type system is ease of changing products and their details; the ease of porting the system to a database table driven system; and the fact that if you ever do port it over, you can use the same Form with some changes in the "Look up" code, meaning that your users do not have to learn a new system.

The major difficulty in designing the system is designing the lists to be usable by a relatively small amount of code.

[white_flag]

Hi Sam

Thanks for the tips!!! (this is almost the same think what I am trying to do)