VBA Match & index with conditions

[eaje92]

Hi all, i am currently trying to create a counter which can help me select an action (A,B,C) depending on the highest value in that state.

Example:
First, the random counter would random between "HIGHEST" or "RANDOM". The probability of the random counter would be 90% "highest" and 10% "random". How i went about doing this is

randomcounter = Int((100 - 1 + 1) * Rnd + 1)
If epsilon > 10 Then
counter = "Q"
Else
counter = "random"
End If

Random Counter

HIGHEST OR RANDOM

<tbody>
</tbody>

After the random counter has chosen between the 2. If it chose "HIGHEST", then based on a state(i would have determined this before), it would chose the highest value and give the corresponding action.

	Action A	Action B	Action C
State1	0	0	0
State2	2	1	3
State3	1	2	3
State4	1	2	3
State5	4	6	7
State6	1	8	6
State7	2	5	9
State8	0	0	1
State9	2	0	3
State10	8	6	0

<tbody>
</tbody>

In this example, my current state is State5. If my counter randoms to a "HIGHEST", im trying to get vba to store "Action C" (being the highest out of 4,6,7).

If the counter randoms to a "Random", the action stored would random between the 2 other action,not the highest value which is "Action A" or "Action C".

i am currently stuck using an index match formula in excel but im trying to implement in vba coding. One way i could think of would be:
If randomcounter = "Highest" and State = 1 Then
Actiontaken = max(range("B6:D6"))

thats really all i got..
Your help is much appreciated!

 

[Marcelo Branco]

See if this does what you want

	A​	B​	C​	D​	E​	F​	G​	H​
1​		Action A​	Action B​	Action C​		State​	Random or Highest​	Result​
2​	State1​	0​	0​	0​		State5​	Random​	Action B​
3​	State2​	2​	1​	3​
4​	State3​	1​	2​	3​
5​	State4​	1​	2​	3​
6​	State5​	4​	6​	7​
7​	State6​	1​	8​	6​
8​	State7​	2​	5​	9​
9​	State8​	0​	0​	1​
10​	State9​	2​	0​	3​
11​	State10​	8​	6​	0​

Given F2 (State)

Formula in G2
=IF(RANDBETWEEN(1,100)>10,"Highest","Random")

Formula in H2
=INDEX(B1:D1,MATCH(LARGE(INDEX(B2:D11,MATCH(F2,A2:A11,0),0),IF(G2="Highest",1,RANDBETWEEN(2,3))),INDEX(B2:D11,MATCH(F2,A2:A11,0),0),0))

Hope this helps

M.

 

[eaje92]

See if this does what you want

	A​	B​	C​	D​	E​	F​	G​	H​
1​		Action A​	Action B​	Action C​		State​	Random or Highest​	Result​
2​	State1​	0​	0​	0​		State5​	Random​	Action B​
3​	State2​	2​	1​	3​
4​	State3​	1​	2​	3​
5​	State4​	1​	2​	3​
6​	State5​	4​	6​	7​
7​	State6​	1​	8​	6​
8​	State7​	2​	5​	9​
9​	State8​	0​	0​	1​
10​	State9​	2​	0​	3​
11​	State10​	8​	6​	0​

<tbody>
</tbody>

Given F2 (State)

Formula in G2
=IF(RANDBETWEEN(1,100)>10,"Highest","Random")

Formula in H2
=INDEX(B1:D1,MATCH(LARGE(INDEX(B2:D11,MATCH(F2,A2:A11,0),0),IF(G2="Highest",1,RANDBETWEEN(2,3))),INDEX(B2:D11,MATCH(F2,A2:A11,0),0),0))

Hope this helps

M.

Hi marcelo, thanks but i would like to store the action taken in vba instead of using formula

 

[Marcelo Branco]

Maybe...

Sub aTest()
    Dim rData As Range, RandomCounter As Long, k As Long
    Dim State As String, lRow As Range, myVal As Variant
    Dim Action As String
    
    'Given
    State = "State5"
    
    'Set data range
    Set rData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    'Get the row that contains State
    Set lRow = rData.Columns(1).Find(State, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If lRow Is Nothing Then
        MsgBox "State Not Found"
        Exit Sub
    End If
        
    'Generate a random integer between 1 - 100
    RandomCounter = Application.RandBetween(1, 100)
    If RandomCounter > 10 Then
        k = 1
    Else
        k = Application.RandBetween(2, 3)
    End If
    myVal = Application.Large(lRow.Resize(, 4), k)
    Action = rData.Rows(1).Cells(lRow.Resize(, 4).Find(myVal, lookat:=xlWhole, LookIn:=xlValues).Column)
End Sub

M.

 

[eaje92]

Maybe...

Sub aTest()
    Dim rData As Range, RandomCounter As Long, k As Long
    Dim State As String, lRow As Range, myVal As Variant
    Dim Action As String
    
    'Given
    State = "State5"
    
    'Set data range
    Set rData = Range("A1:D" & Cells(Rows.Count, "A").End(xlUp).Row)
    'Get the row that contains State
    Set lRow = rData.Columns(1).Find(State, LookIn:=xlValues, lookat:=xlWhole, SearchOrder:=xlByRows, _
                SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If lRow Is Nothing Then
        MsgBox "State Not Found"
        Exit Sub
    End If
        
    'Generate a random integer between 1 - 100
    RandomCounter = Application.RandBetween(1, 100)
    If RandomCounter > 10 Then
        k = 1
    Else
        k = Application.RandBetween(2, 3)
    End If
    myVal = Application.Large(lRow.Resize(, 4), k)
    Action = rData.Rows(1).Cells(lRow.Resize(, 4).Find(myVal, lookat:=xlWhole, LookIn:=xlValues).Column)
End Sub

M.

hi Marcelo, thanks for your help but this isnt really what im looking for. After finding the row that the state is in, is there a to search for the Action with the highest value?

 

[Marcelo Branco]

hi Marcelo, thanks for your help but this isnt really what im looking for. After finding the row that the state is in, is there a to search for the Action with the highest value?

The code does what you requested in post 1. Once the row is found, it gets the highest value and corresponding action (probability 90%) or the second or third largest (probability of 5% each) and the corresponding actions.

M.

 

[eaje92]

The code does what you requested in post 1. Once the row is found, it gets the highest value and corresponding action (probability 90%) or the second or third largest (probability of 5% each) and the corresponding actions.

M.

hmm im always stuck in "state not found"

Because my table is found from range A18:D30, i changed part of the code to Set rData = Range("A18:D" & Cells(Rows.Count, "A").End(xlUp).Row)
and State = Range("L10").Value as my state would dynamically change over time.