Need help please with excel sheet

magiciansmask

New Member
Joined
Nov 15, 2019
Messages
10
I found a sheet online and would love to make my own. The problem is, I'm not sure how one thing occurs. When you enter the date on column row A15, it opens up other rows as well. How is this done?


This is what was in the visual basic


Private Sub worksheet_change(ByVal Target As Excel.Range)
Dim numBets As String
Dim numCharsInBet As Long
Dim numCharsPrevLocation As Long
Dim MyBet(1) As String
Dim MyWon(4) As String


On Error GoTo myError

If Target.Column < 12 Then
If Target.Row > 14 Then
If Target.Column = 1 Then
MyBet(0) = ""
MyBet(1) = "BET"

MyWon(0) = "Won"
MyWon(1) = "Lost"


With Range("Z" & Target.Row).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlValidateList, Formula1:=Join(MyBet, ",")
'.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

With Range("K" & Target.Row).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlValidateList, Formula1:=Join(MyWon, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

If IsDate(Target.Value) Then
If Target.Value > Cells(Target.Row - 1, Target.Column).Value Then
If Target.Row > 15 And Range("E11").Value <> "" Then
'Adding count to previous row
numBets = Range("E11").Value
numCharsInBet = Len(numBets)
numCharsPrevLocation = Len(Cells(Target.Row - 1, Target.Column + 1).Value)
With Cells(Target.Row - 1, Target.Column + 1)
.Value = .Value & " " & numBets
With .Characters(Start:=numCharsPrevLocation + 1, Length:=numCharsPrevLocation + numCharsInBet).Font
.Color = -16776961
End With
End With
'Putting border
With Range(Cells(Target.Row, 1), Cells(Target.Row, 40)).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlMedium
End With
End If
Range("E11").Value = Range("E11").Value + 1
Else
If Target.Row = 15 Then
Range("E11").Value = 1
End If
End If
End If

Range("N" & Target.Row).NumberFormat = "$#,##0.00"
Range("Q" & Target.Row).NumberFormat = "$#,##0.00"
Range("S" & Target.Row).NumberFormat = "$#,##0.00"

Range("L" & Target.Row).Formula = "=IF(K" & Target.Row & "=" & """" & "Won" & """" & ", " & """" & "Bet Win" & """" & "," & """" & "Bet Lost" & """" & ")" '"=IF(K" & Target.Row & "=" & """" & "Won" & """" & ",IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "BW" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LOST" & """" & "," & """" & "G/T" & """" & ")),IF(I" & Target.Row & "=" & """" & "BET" & """" & "," & """" & "LOST" & """" & ",IF(I" & Target.Row & "=" & """" & "" & """" & "," & """" & "LW" & """" & "," & """" & "G/T" & """" & ")))"
Range("N" & Target.Row).Formula = "=IF(Z" & Target.Row & "=" & """" & "TRADE" & """" & ",J" & Target.Row & ",IF(I" & Target.Row & "=" & """" & "GREEN" & """" & ",J" & Target.Row & ",IF(E" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",(E" & Target.Row & "*G" & Target.Row & "-E" & Target.Row & ")*(1-M" & Target.Row & "),-E" & Target.Row & "),IF(F" & Target.Row & ">0,IF(K" & Target.Row & "=" & """" & "WON" & """" & ",-F" & Target.Row & "*H" & Target.Row & "+F" & Target.Row & ",F" & Target.Row & "*(1-M" & Target.Row & "))))))"

If Target.Row > 15 Then
Range("O" & Target.Row).Formula = "= O" & Target.Row - 1 & " + N" & Target.Row
Else
Range("O" & Target.Row).Formula = "=N" & Target.Row
End If

End If


If Range("N" & Target.Row).Value >= 0 Then
Range("N" & Target.Row).Font.Color = vbBlack
Range("Q" & Target.Row).Formula = "=N" & Target.Row
Range("Q" & Target.Row).Font.Color = vbBlack
Range("S" & Target.Row).Formula = ""
Else
Range("N" & Target.Row).Font.Color = vbRed
Range("S" & Target.Row).Font.Color = vbRed
Range("S" & Target.Row).Formula = "=N" & Target.Row
Range("Q" & Target.Row).Formula = ""
End If

If UCase(Range("K" & Target.Row).Value) <> UCase("Won") Then
Range("N" & Target.Row).Font.Color = vbRed
Else
Range("N" & Target.Row).Font.Color = vbBlack
End If

Range("Z13").Formula = "=O" & Target.Row & "/e11"


End If
End If

GoTo exiter
myError:
GoTo exiter
exiter:
End Sub
 

Attachments

  • Screenshot (22).png
    Screenshot (22).png
    78.6 KB · Views: 2
  • Screenshot (21).png
    Screenshot (21).png
    72 KB · Views: 2

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
so you want an explanation of how it works?

or do you need assistance to get it to work?
 

magiciansmask

New Member
Joined
Nov 15, 2019
Messages
10

ADVERTISEMENT

An explanation of how it works, please? I want to learn :)
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
If you go to the code window, and place the cursor in this line and press F9. Then go back the spreadsheet and enter a date in cell A15, you will be in the code window. You can press F8 to go through the code line be line. You may be able to work out what is happening.
There is a lot of code to try and explain. Post back if you can't figure something out.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,656
Messages
5,573,435
Members
412,529
Latest member
cTatch
Top