optimizing vba code or suggestion of solution

[tonypiha]

Hi all,
I play poker and I use an excel with specific ranges of cards. The cards you will call a bet depends on the position of the raiser and the position you are calling. I have matrix with the cards with named ranges
The position´s name are:
EP
MP
CO
BTN
SB
BB

So if the EP open raise with a bet and I am sitting at MP, I have a range called Call_MP_vs_EP
So I did 2 columns of buttons that assign the respective string to a cell ( here, P40 and Q40)and the I have a button that run a macro to call the specific range
The Macro is below. I am wondering if there is a smarter way to do it
And it is not possible to have the same positions, for instance EP vs EP


Private Sub MP_Click()


' range call of MP vs EP
Dim Raise As Variant
Dim vscall As Variant
Dim atencao As String


'vscall = define caller position


'Raise = define raiser position


vscall = Sheets("Dashboard").Range("p40").Value
Raise = Sheets("Dashboard").Range("q40").Value

Application.ScreenUpdating = False


'delete previous range
Sheets("Dashboard").Range("B3:N20").Delete

'Not possible to have 2 positions at same time, then go to error msg


On Error GoTo atencao
'Select, copy and past the defined range based on Q40 and P40 cell value


Application.Range("Call_" & vscall & "_vs_" & Raise).Copy
Sheets("Dashboard").Select
Sheets("Dashboard").Range("B4").Select
Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
, SkipBlanks:=False, Transpose:=False

On Error GoTo atencao
Range("A1").Select
Application.ScreenUpdating = True

atencao: MsgBox "ranges inválidos"


End Sub

 

[JackDanIce]

What is the name of the other sheet?

The above code test errors because that range is not on the sheet Dashboard. This may be why the earlier suggestion was not working.

 

[tonypiha]

same error of application or object definition

 

[tonypiha]

Call vs UTG

 

[JackDanIce]

Try:

Private Sub MP_Click()
    
    Dim strRange    As String
    
    strRange = "Call_@P40_vs_@Q40"
    
    With Sheets("Dashboard")
        .Select
        
        If .Cells(40, 16).Value = .Cells(40, 17).Value Then
            MsgBox "ranges inválidos", vbExclamation, "Ranges Inválidos"
            Exit Sub
        End If
        
        Application.ScreenUpdating = False
        
        .Cells(3, 2).Resize(18, 13).ClearContents
        
        strRange = Replace(strRange, "@P40", .Cells(40, 16).Value)
        strRange = Replace(strRange, "@Q40", .Cells(40, 17).Value)
                
        Sheets("Call vs UTG").Range(strRange).Copy
        .Cells(4, 2).PasteSpecial Paste:=xlPasteFormats
        .Cells(4, 2).PasteSpecial Paste:=xlPasteValues
                    
    End With
    
    With Application
        .CutCopyMode = False
        .Goto Cells(1, 1), True
        .ScreenUpdating = True
    End With
    
End Sub

 

[tonypiha]

it worked
thank you
Just one more point. I have other sheets with named ranges, do I need to manually define where is the ranges?
I have for ex another sheet called
Call vs MP
Call vs CO

 

[JackDanIce]

You're welcome. If that's how it worked for this case, I suspect so, however, if your named ranges in the sheet are global names, then I'd expect that line of code to work without stating the name of the sheet.

 

[tonypiha]

Hi found out what is going on
When I copy paste the code in the module it works, when I run it under the worksheets it get the error.
I used active X controls
is there a way to correct that?

 

[JackDanIce]

There is a difference between code in modules and code in worksheet objects which trigger events.

The two won't easily work interchangeably so it might require a redesign of your spreadsheet.