multiple if or case select

**john55** · 08-08-2014, 05:02 AM

Hello guys,

I tried to find a way of having a correct value in cells c14, c15, c16 according to textbox(s) value.
I attached a test file for yr refference.

I appreciate your help!

**buran** · 08-08-2014, 06:12 AM

Hi,
If I understand right, you can achieve it without VBA code, just formula

in c14, c15 put

=COUNTIFS(C3:C6,"<>-",C3:C6,"<>")*2

in c16:

=COUNTIFS(C3:C6,"<>-",C3:C6,"<>")

**john55** · 08-08-2014, 06:28 AM

thx a lot for yr solution! it works! I want to use it in a large project and I need a vba solution, if I do not find one I'll try to incorporate this one!

**buran** · 08-08-2014, 06:48 AM

it's easy to incorporate it in several different ways:

    With Range("C14")
        .FormulaR1C1 = _
        "=COUNTIFS(R[-11]C:R[-8]C,""<>-"",R[-11]C:R[-8]C,""<>"")*2"
        .Value = .Value    'Comment out this line if you want to keep formula, not value
    End With

or

Range("C14").Value = WorksheetFunction.CountIfs(Range("C3:C6"), "<>-", Range("C3:C6"), "<>") * 2

**nilem** · 08-08-2014, 06:51 AM

Hi John55,
maybe this

Private Sub CommandButton1_Click()
Dim t&
Range("C3:C6").Value = Application.Transpose(Array(TextBox1, TextBox2, TextBox3, TextBox4))
t = -(Len(TextBox1.Value) > 1) - (Len(TextBox2.Value) > 1) - _
    (Len(TextBox3.Value) > 1) - (Len(TextBox4.Value) > 1)
Range("C14:C16").Value = Application.Transpose(Array(2 * t, 2 * t, t))
End Sub

**john55** · 08-08-2014, 06:56 AM

Thanks both of you!

multiple if or case select

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

multiple if or case select

Re: multiple if or case select

Re: multiple if or case select

Re: multiple if or case select

Re: multiple if or case select

Re: multiple if or case select

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] is if-then or select case statement with multiple arguments possible?

Using Case Select On Range With Multiple Conditions?

Multiple IF statements, or a select case?

Select Case VBA with multiple criteria

Select case / case is, multiple arguments

Bookmarks

Bookmarks

Posting Permissions