Help with Formula

[Hjemmet]

I need som Help with this Formula

Nearly everything works in this formula but Not the line in Red

Private Sub ComboBox1_Change()   If ComboBox1.ListIndex = -1 Or ComboBox2.ListIndex = -1 Then Exit Sub
   
   For j = 1 To 3
      Me("TextBox" & j) = ComboBox2.Column(j + 3 * ComboBox1.ListIndex)
   Next
End Sub
Private Sub ComboBox2_Change()
    M_calc
End Sub
Private Sub TextBox1_Change()
    M_Box 1
End Sub
Private Sub TextBox2_Change()
    M_Box 2
End Sub
Private Sub TextBox3_Change()
    M_Box 3
End Sub
Sub M_Box(Y)
    ComboBox2.Column(Y + 3 * ComboBox1.ListIndex) = Me("TextBox" & Y)
    M_calc
End Sub
Sub M_calc()
    ReDim sp(6)
    sq = sp
    
    With ComboBox2
       For j = 1 To 19 Step 3
           c00 = c00 + Val(.Column(j + 0))
           sp(j \ 3) = Val(.Column(j + 1))
           sq(j \ 3) = Val(.Column(j + 2))
       Next
       .Column(22) = c00
       .Column(23) = Application.Max(sp)
       .Column(24) = Application.Min(sq)
       Frame1.Caption = Space(2) & .Column(22) & Space(14) & .Column(23) & Space(10) & .Column(24)
    End With
End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Ark1.Cells(1).CurrentRegion.Offset(2).Value = ComboBox2.List
End Sub

if i Change the Line to this Then it Works

.Column(24) = Application.Max(sq)

but That is Not what I want

Link to my File https://www.dropbox.com/s/y6h6hbtsxd...good.xlsm?dl=0

[Dave]

Place "Option Explicit" at the top of your code module and Dim your variables. It should be...

Application.worksheetfunction.Max(sq)

and the same for Min. HTH. Dave

[Hjemmet]

sorry BUT i am Not a Guru to Vba Code

my way to Get those File work is Fishing around Vba Forum's and get Help there and then Make it work

just Like Cut'n Past

[Dave]

?????? If U don't want help then don't ask. I'm done. Dave

[Hjemmet]

Hm Weird Answer

Of Cause i want Help But i Dont Understand HOW to Program The Dim Function

[Paul_Hossler]

'Dim' = dimension = define variable and type (see online Help)

'Option Explicit' = Require all variables to be explicitly Dim-ed (see online Help)

I think your original code

sq = sp

made sq a variant with an array in it (not sure)

Use the [Go Advanced] and the paperclip icon to attach a file to your post, not Dropbox or some other file storage site

but That is Not what I want

What do you want?


I didn't try to run anything

 Option Explicit     '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

Private Sub ComboBox1_Change()
    Dim j As Long   '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    If ComboBox1.ListIndex = -1 Or ComboBox2.ListIndex = -1 Then Exit Sub


   For j = 1 To 3
      Me("TextBox" & j) = ComboBox2.Column(j + 3 * ComboBox1.ListIndex)
   Next
End Sub


Private Sub ComboBox2_Change()
    M_calc
End Sub


Private Sub TextBox1_Change()
    M_Box 1
End Sub


Private Sub TextBox2_Change()
    M_Box 2
End Sub


Private Sub TextBox3_Change()
    M_Box 3
End Sub


Sub M_Box(Y)
    ComboBox2.Column(Y + 3 * ComboBox1.ListIndex) = Me("TextBox" & Y)
    M_calc
End Sub


Sub M_calc()
    Dim sp(0 To 6) As Double    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    Dim sq(0 To 6) As Double    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'    ReDim sp(6)                '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
'    sq = sp                    '   <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
    
    With ComboBox2
       For j = 1 To 19 Step 3
           c00 = c00 + Val(.Column(j + 0))
           sp(j \ 3) = Val(.Column(j + 1))
           sq(j \ 3) = Val(.Column(j + 2))
       Next
       .Column(22) = c00
       .Column(23) = Application.Max(sp)
       .Column(24) = Application.Min(sq)
       Frame1.Caption = Space(2) & .Column(22) & Space(14) & .Column(23) & Space(10) & .Column(24)
    End With
End Sub




Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
   Ark1.Cells(1).CurrentRegion.Offset(2).Value = ComboBox2.List
End Sub

[Hjemmet]

Thanks for give me a Hint in the right Direction Paul Hossler.

i have try your Code but it aint Work...

now what i want is the Value been set in Cell area like they other values and Colomn Y been show total Value for But It aint work


Doubleclick on Sheet's "H-erne" in cell Area B3:V130 then Chose a Player and Cupgame then you could enter Value in
textbox for "180" and "Higest" and "No"

the function works for "180" and "Higest" but not for "NO"

the 180 is a Maximum Score with 3 Trown Dart's
the Highest is where to Put a Player's Highest Out score it could be from "2 to 170 "
the "No" is How many Dart's a Player used to get down from start to end normaly game is 501 to 0 where 9 Dart's is the Fastest
these Infor is only to explain my Idea

i put a Video sequence on what happend

[SamT]

When j = 1 there is no such thing as an array index = 0.333...

For j = 1 To 19 Step 3
           c00 = c00 + Val(.Column(j + 0))

If j = 1 then 
   i = 0
Else
    i = j / 3
End If

           sp(i) = Val(.Column(j + 1))
           sq(i) = Val(.Column(j + 2))

Dont Understand HOW to Program The Dim Function

Type/enter "Dim" onto a VBA code page. Place the cursor inside "Dim". Press F1 while connected to the Internet. Repeat for each Term you don't understand.

ps: Knowing "Dim" for VBA is like knowing "+" when learning arithmetic.

[Hjemmet]

Aint Working

[Aussiebear]

Aint Working

Well that explains a lot. Try being a little more precise by telling us what isnt working.

[Hjemmet]

SamT have give me a change in to the original Code
But that aint work as it should

the Original Work for some Part of it But not that part there was
markt as red

[p45cal]

Hjemmet
Your code seems to work as intended here, with no error.
I can't reproduce the error.
Could you tell us how to get the error, which player to choose, whether a specific Cup should be chosen. Attach another workbook if necessary.

Penny drops (I can't open your recording.zip).
You're seeing a zero for the min value when there at least one blank in Dart's columns of that player.
That's because there are zeroes in sq, and that's because you've used Val()
Try removing the Val bits:

           c00 = c00 + .Column(j + 0)
           sp(j \ 3) = .Column(j + 1)
           sq(j \ 3) = .Column(j + 2)

(And keep the redim sp(6) without the As Double otherwise the zeroes will return)
SamT , note that the original code uses a backslash, not a forward slash for the division, I think it's call integer division, and it returns a valid index (a whole number) for the sp and sq arrays.

[Hjemmet]

P45cal

you need the program Winzip to open it other wise click on this Link

https://www.dropbox.com/s/46hvee4je36ko64/Recording%20%235.mp4?dl=0

The Error Appear from start to end when i use the userform......


the same error came on every Player and in each Cup game's

thanks for take a Look on my Problem...

[p45cal]

Well…
my earlier solution didn't work. The reason, when you assign a value to a single member of a combobox list as you do with:

ComboBox2.Column(Y + 3 * ComboBox1.ListIndex) = Me("TextBox" & Y)

it is a string. Hence the presence of Val() in the M_calc sub code.
The problem with Val() is that it converts a blank/empty value to zero, and later when you apply Application.Min to it, zero is the smallest value.
So I thought the answer would be to make sure that combobox list contains numbers (doubles) as it does when it's first assigned values from a range, so a change to:
ComboBox2.Column(Y + 3 * ComboBox1.ListIndex) = Val(Me("TextBox" & Y))
should do it.
No such luck. Even if you try to assign a number (or even a date) like that, it becomes a string in the combobox.list. Why!? A combobox.list is perfectly capable of holding multiple sorts of data types.

So ignore all that and here's a work around, change one line:

sq(j \ 3) = Val(.Column(j + 2))

to:

sq(j \ 3) = IIf(Len(.Column(j + 2)) > 0, Val(.Column(j + 2)), Empty)

[Hjemmet]

WOOOOOOOOOOWWWWWWWW With That Change it GOD DAME WORK as it SHould

THANKYOU so Much for you Time "p45cal"

thankyou.jpg
Your Regards From Denmark.