VBA, Check only selected betting row with entire results

Kishan · Nov 20, 2017

Using Excel 2000

Hi,

I got results in the columns C:P (R1 Through R14)
I got betting sets in the columns S:AF (B1 Through B14)

Bit complicate, I want to check any "1" betting ROW with entire results

For now I am using the formula which check if find grater than 9
Formula is in the cell Q6 and copied to down

Code:

=IF(SUMPRODUCT(--($S$6:$AF$6=C6:P6))>9,SUMPRODUCT(--($S$6:$AF$6=C6:P6)),"")

What is the problem I need to change formula every time I want to check desire row

I require VBA solution, which can give me an input option where I can enter desire combi nº is shown in column R and macro could populate the result in column Q

Please need help with this...

Example results in column Q are shown by above formula

Book1

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

Z

AA

AB

AC

AD

AE

AF

AG

1

2

3

4

5

R1

R2

R3

R4

R5

R6

R7

R8

R9

R10

R11

R12

R13

R14

EM

Combi n

B1

B2

B3

B4

B5

B6

B7

B8

B9

B10

B11

B12

B13

B14

6

X

1

2

1

2

1

2

X

1

X

1

2

1

X

1

2

1

7

X

2

1

X

1

2

1

X

2

1

2

1

2

1

X

1

2

1

2

X

2

1

8

X

1

2

1

2

1

2

X

1

X

1

3

2

1

X

2

1

X

2

1

2

X

1

2

9

2

1

X

2

1

X

1

2

1

11

4

1

X

2

X

1

X

1

2

10

1

X

1

X

1

2

1

X

1

5

1

2

X

1

2

1

2

1

X

2

1

X

1

X

11

X

2

X

2

X

1

2

1

2

6

X

1

X

2

1

X

1

X

2

1

12

X

1

2

1

2

1

X

1

X

1

7

2

1

X

1

X

1

X

2

1

X

13

1

X

2

1

X

1

2

1

X

1

8

2

X

1

2

1

X

2

X

14

1

2

X

1

X

1

X

9

X

2

X

1

2

X

1

2

15

1

X

1

X

1

2

X

1

10

1

X

2

1

X

2

1

X

1

X

16

X

1

X

2

1

2

X

1

X

11

X

1

X

1

2

1

17

1

X

1

2

1

X

1

2

1

X

1

12

2

X

1

2

1

2

1

X

18

1

X

1

2

X

1

10

13

X

1

2

X

2

1

2

X

1

19

X

1

X

1

2

1

X

14

1

2

X

1

2

X

1

X

20

1

2

1

X

1

2

X

1

X

2

15

1

X

1

X

1

2

X

2

X

21

1

2

1

X

1

2

1

X

1

16

1

2

1

2

1

X

22

2

1

X

1

X

1

2

1

2

17

X

2

1

X

2

X

2

1

X

1

2

X

2

23

1

2

1

2

1

X

1

18

X

1

X

1

2

1

2

1

2

1

24

X

1

2

1

2

1

X

1

X

19

1

X

1

2

1

X

1

X

25

1

X

1

20

2

1

X

2

1

2

1

2

X

1

X

26

X

1

2

1

X

1

X

1

21

2

1

X

2

X

2

1

X

2

1

27

1

2

X

1

2

1

X

1

22

1

X

1

2

1

X

1

X

1

X

1

28

1

2

1

2

X

1

X

1

X

23

X

2

1

2

1

X

1

2

1

2

1

29

1

2

1

X

1

X

1

2

1

X

2

24

1

X

1

X

1

X

30

1

X

1

X

1

X

1

2

25

1

2

X

1

2

1

2

1

X

1

2

X

31

1

X

1

10

26

2

X

2

X

2

1

2

1

2

1

32

1

X

1

2

1

X

1

X

2

1

27

2

1

X

1

X

1

X

1

X

1

33

2

1

X

1

X

2

1

2

1

X

1

28

2

1

X

2

X

1

X

1

2

X

2

X

34

1

X

1

2

1

X

1

X

1

29

X

1

2

1

X

2

1

X

1

35

2

1

2

X

1

X

1

X

1

30

1

2

1

X

1

2

X

2

X

1

X

36

1

X

2

1

2

X

1

X

1

2

X

31

1

X

1

2

1

X

1

X

1

37

X

1

2

X

32

X

1

2

X

2

1

X

1

X

38

X

2

1

X

1

2

X

1

33

X

2

1

2

1

2

X

1

X

39

X

1

2

1

2

1

X

1

2

34

1

2

1

2

X

1

X

1

2

1

40

1

X

1

2

X

1

2

1

X

1

35

2

1

2

1

2

X

2

X

2

41

X

1

X

1

X

1

36

X

1

X

2

1

2

X

2

42

2

X

2

1

X

1

X

1

X

1

2

1

37

1

2

X

1

X

2

1

X

1

X

43

X

1

2

X

1

2

1

2

1

38

1

X

1

X

2

1

2

X

1

2

1

44

X

1

X

1

X

1

2

1

X

39

1

2

1

X

1

2

1

X

1

2

1

2

45

1

X

1

2

1

X

1

40

X

2

1

X

46

1

X

2

1

X

1

41

1

2

1

2

1

2

1

2

47

2

X

1

X

1

X

1

42

1

2

1

X

2

X

1

48

1

2

1

X

2

1

2

1

43

2

1

2

X

1

X

1

X

2

1

49

X

1

X

1

2

1

2

1

X

2

X

2

1

44

1

X

2

1

X

1

X

1

2

1

50

X

1

X

1

2

45

1

X

2

X

2

1

2

1

X

1

X

1

51

sheet1

Thank you in advance

Regards,
Kishan

Kishan · Nov 21, 2017

Hi,

Searching in MrExcel, I found how to put formula in VBA the code below fill formula in the column Q and after it leave the value, it is working ok.

Code:

Sub FillFormlaInQ()
    
    Dim lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row

    Range("Q6:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$6:$AF$6=C6:P6))>9,SUMPRODUCT(--($S$6:$AF$6=C6:P6)),"""")"
    Range("Q6:Q" & lngLastRow) = Range("Q6:Q" & lngLastRow).Value
    
End Sub

I tried modifying following code to get row number from cell Q2

Code:

Sub FillFormulaColumnInQ_UsingRowNumber_FromCellValue()
    
    
    Dim RN As Long, lngLastRow As Long
    lngLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    
    RN = Worksheets("Sheet1").Cells(2, "Q").Value
    

    
    Range("Q6:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$RN:$AF$RN=C6:P6))>9,SUMPRODUCT(--($S$RN:$AF$RN=C6:P6)),"""")"
    Range("Q6:Q" & lngLastRow) = Range("Q6:Q" & lngLastRow).Value
    
End Sub

but it seems does not work it gives error 1004 and highlight following line

Code:

Range("Q6:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$RN:$AF$RN=C6:P6))>9,SUMPRODUCT(--($S$RN:$AF$RN=C6:P6)),"""")"

First I need help how can be applied the cell value in the code to get row number

Secondly is it possible instead of filling row number can I get VBA with input box where I can insert any combi nº value from Column R may be

Regards,
Kishan

Kishan · Nov 21, 2017

Hi,

I think May it is not possible to create input box to get work with combi nº

May someone can help to get row number is shown in red from cell Q2 it will be Ok, and how can be applied in VBA

Code:

    Range("Q[B][COLOR=#ff0000]6[/COLOR][/B][COLOR=#000000]:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$[/COLOR][COLOR=#ff0000][B]6[/B][/COLOR][COLOR=#000000]:$AF$[/COLOR][B][COLOR=#ff0000]6[/COLOR][/B][COLOR=#000000]=C6:P6))>9,SUMPRODUCT(--($S$[/COLOR][B][COLOR=#ff0000]6[/COLOR][/B][COLOR=#000000]:$AF$[/COLOR][COLOR=#ff0000][B]6[/B][/COLOR][COLOR=#000000]=C6:P6)),"""")  [/COLOR]

Thank you in advance

Regards,
Kishan

bellman101 · Nov 21, 2017

Hi Kishan,

You're more or less right. RN is a row number variable, but the code is instead entering the text "RN" as part of the formula, so it is trying to look at Cell $AF$RN, which of course isn't a thing!

Try this:

Code:

Range("Q6:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$" & RN & ":$AF$" & RN & "=C6:P6))>9,SUMPRODUCT(--($S$" & RN & ":$AF$" & RN & "=C6:P6)),"""")

Cheers
JB

Marcelo Branco · Nov 21, 2017

A solution using formulas

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1

[/TD]
[TD]

Desired Comb

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2

[/TD]
[TD]

6

[/TD]
[/TR]
</tbody>[/TABLE]

Enter the desired combination in A2

Change the formula in Q6 to
=IF(SUMPRODUCT(--(INDEX(S:AF,A$2,0)=C6:P6))>9,SUMPRODUCT(--(INDEX(S:AF,A$2,0)=C6:P6)),"")
copy down

Hope this helps

M.

Kishan · Nov 21, 2017

bellman101 said:
Hi Kishan,

You're more or less right. RN is a row number variable, but the code is instead entering the text "RN" as part of the formula, so it is trying to look at Cell $AF$RN, which of course isn't a thing!

Try this:

Code:

Range("Q6:Q" & lngLastRow).Formula = "=IF(SUMPRODUCT(--($S$" & RN & ":$AF$" & RN & "=C6:P6))>9,SUMPRODUCT(--($S$" & RN & ":$AF$" & RN & "=C6:P6)),"""")

Cheers
JB

Hi bellman101, code is working flawless!!

Do you have idea instead of applying a row number in the cell Q2 does it is possible to have input box, I mean after the code is run pop up input box where I can insert the row number?

Thank you very much for your time and help

Regards,
Kishan

Marcelo Branco · Nov 21, 2017

Kishan said:
Do you have idea instead of applying a row number in the cell Q2 does it is possible to have input box, I mean after the code is run pop up input box where I can insert the row number?

Have you tried my suggestion in post 5 (no VBA)?

M.

bellman101 · Nov 21, 2017

Yup,

replace the RN = with this:

RN = Inputbox ("What Row Number please Kishan?")

beware without error handling, your code will fail if you enter a non-numeric, or cancel or click the red x in the input box.

Kishan · Nov 21, 2017

Marcelo Branco said:
A solution using formulas

Try

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1
[/TD]
[TD]

Desired Comb
[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2
[/TD]
[TD]

6

[/TD]
[/TR]
</tbody>[/TABLE]

Enter the desired combination in A2

Change the formula in Q6 to
=IF(SUMPRODUCT(--(INDEX(S:AF,A$2,0)=C6:P6))>9,SUMPRODUCT(--(INDEX(S:AF,A$2,0)=C6:P6)),"")
copy down

Hope this helps

M.

Hi Marcelo Branco, I never thought it could be done this way it is very practical, brilliant idea!!

I like your formula non-vba solution!!

Thank you very much for your time and help

Regards,
Kishan

Kishan · Nov 21, 2017

bellman101 said:
Yup,

replace the RN = with this:

RN = Inputbox ("What Row Number please Kishan?")

beware without error handling, your code will fail if you enter a non-numeric, or cancel or click the red x in the input box.

Thanks bellman101, for giving a code, which enter a row number via input box, it is very perfect!!

Thank you once again for you help

Regards,
Kishan

VBA, Check only selected betting row with entire results

Kishan

Well-known Member

Excel Facts

Kishan

Well-known Member

Kishan

Well-known Member

bellman101

Board Regular

Marcelo Branco

MrExcel MVP

Kishan

Well-known Member

Marcelo Branco

MrExcel MVP

bellman101

Board Regular

Kishan

Well-known Member

Kishan

Well-known Member

Similar threads

Forum statistics

Share this page

VBA, Check only selected betting row with entire results

Well-known Member

Excel Facts

Well-known Member

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

MrExcel MVP

Board Regular

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock