Extract information out of a cell.

Ramballah

Ramballah

Active Member
Joined
Sep 25, 2018
Messages
334
Office Version
  1. 365
Platform
  1. Windows
Well hello everyone...
I have posted this before, but since i got no replies or help, i thought i would repost it and ask it a bit differently or something.
So i have an sheet where i want to keep track of my coinflips.. But to make life easy i want to just write down simple things and then excel has to extract the information from that cell
into my charts
in the end it has to look something like this:

[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Number[/TD]
[TD]Winstreak[/TD]
[TD]Amount BET[/TD]
[TD]Whats bet on[/TD]
[TD]Win/Lose[/TD]
[TD]What the coin landed on[/TD]
[TD]Profits[/TD]
[TD]Person[/TD]
[/TR]
[TR]
[TD]50 t[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]1[/TD]
[TD]-[/TD]
[TD="align: right"]50[/TD]
[TD]Tails[/TD]
[TD]Lose[/TD]
[TD]Heads[/TD]
[TD]$ -50[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]5 h[/TD]
[TD]win[/TD]
[TD]shee[/TD]
[TD="align: right"]2[/TD]
[TD]-[/TD]
[TD="align: right"]5[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]-[/TD]
[TD]Shee[/TD]
[/TR]
[TR]
[TD]50 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]50[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 50[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]50 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]50[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 50[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]600 h[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]600[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]$ 600[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]50 t[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]6[/TD]
[TD]-[/TD]
[TD="align: right"]50[/TD]
[TD]Tails[/TD]
[TD]Lose[/TD]
[TD]Heads[/TD]
[TD]$ -50[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]250 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]250[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 250[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]500 h[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]8[/TD]
[TD]-[/TD]
[TD="align: right"]500[/TD]
[TD]Heads[/TD]
[TD]Lose[/TD]
[TD]Tails[/TD]
[TD]$ -500[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]500 h[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]9[/TD]
[TD]-[/TD]
[TD="align: right"]500[/TD]
[TD]Heads[/TD]
[TD]Lose[/TD]
[TD]Tails[/TD]
[TD]$ -500[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]1000 h[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]10[/TD]
[TD]-[/TD]
[TD="align: right"]1000[/TD]
[TD]Heads[/TD]
[TD]Lose[/TD]
[TD]Tails[/TD]
[TD]$ -1,000[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]1500 h[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]11[/TD]
[TD]-[/TD]
[TD="align: right"]1500[/TD]
[TD]Heads[/TD]
[TD]Lose[/TD]
[TD]Tails[/TD]
[TD]$ -1,500[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]319 h[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]319[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]$ 319[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]100[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]100[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]200 h[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]15[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]200[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]$ 200[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]200 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]16[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]200[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 200[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 h[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]17[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]100[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]73 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]18[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]73[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 73[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]19[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]100[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 h[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100[/TD]
[TD]Heads[/TD]
[TD]Win[/TD]
[TD]Heads[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 t[/TD]
[TD]win[/TD]
[TD]me[/TD]
[TD="align: right"]21[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]100[/TD]
[TD]Tails[/TD]
[TD]Win[/TD]
[TD]Tails[/TD]
[TD]$ 100[/TD]
[TD]Me[/TD]
[/TR]
[TR]
[TD]100 t[/TD]
[TD]lose[/TD]
[TD]me[/TD]
[TD="align: right"]22[/TD]
[TD]-[/TD]
[TD="align: right"]100[/TD]
[TD]Tails[/TD]
[TD]Lose[/TD]
[TD]Heads[/TD]
[TD]$ -100[/TD]
[TD]Me[/TD]
[/TR]
</tbody>[/TABLE]
As you can see, on the left I put in 100 t lose me for example
meaning, i bet 100$ on tails but i lost, and i was the one doing it. (sometimes there are other people also coinflipping)
Now i need this simplified information to be put in the charts, and then with formulas i can probably figure out the rest.
And also if its not to hard to do, is it possible to have it at 1 cell where i type my simplified info, then it gets put into the charts and cleans the cell where i put in my info? so i dont have to go over 4000 lines for example.
i hope i cleared my question from my previous post.
I'm not sure how the rules work if its allowed but i will put the link of my previous post here
https://www.mrexcel.com/forum/excel-questions/1098440-coinflipping-charts.html

Yours sincerely,
Ramballah
[TABLE="class: cms_table"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
I am going to give you Change event code (install instructions below) which means that as soon as you enter the third value of your three-part entry, the proper line of code will be added to the bottom of the table. In order for this code to work correctly, you must have the following layout on your worksheet (all of the following are text entries into the indicated cell)...

Cell A1: Roll ==>
Cell A2: Number
Cell B2: Winstreak
Cell C2: Amount BET
Cell D2: What's bet on
Cell E2: Win/Lose
Cell F2: What the coin landed on
Cell G2: Profits
Cell H2: Person

You will make your entries in cells B1 through D1 (where cell B1 would get the amount bet followed by a space followed by either an h or t, where cell C1 would get either the word win or lose and D1 would get the persons name (your name must always be the word me). Okay, with that said, here is the code...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    If Cells(Rw, "G").Value = Int(Cells(Rw, "G").Value) Then
      Cells(Rw, "G").NumberFormat = "$ 0"
    Else
      Cells(Rw, "G").NumberFormat = "$ 0.00;$ -0.00"
    End If
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    [B1:D1] = ""
    Range("B1").Select
  End If
End Sub[/td]
[/tr]
[/table]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
 
Upvote 0
This is perfect!!!!!! Thanks so much its working so perfect!!!
 
Upvote 0
Rick Rothstein said:
I am going to give you Change event code (install instructions below) which means that as soon as you enter the third value of your three-part entry, the proper line of code will be added to the bottom of the table. In order for this code to work correctly, you must have the following layout on your worksheet (all of the following are text entries into the indicated cell)...

Cell A1: Roll ==>
Cell A2: Number
Cell B2: Winstreak
Cell C2: Amount BET
Cell D2: What's bet on
Cell E2: Win/Lose
Cell F2: What the coin landed on
Cell G2: Profits
Cell H2: Person

You will make your entries in cells B1 through D1 (where cell B1 would get the amount bet followed by a space followed by either an h or t, where cell C1 would get either the word win or lose and D1 would get the persons name (your name must always be the word me). Okay, with that said, here is the code...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    If Cells(Rw, "G").Value = Int(Cells(Rw, "G").Value) Then
      Cells(Rw, "G").NumberFormat = "$ 0"
    Else
      Cells(Rw, "G").NumberFormat = "$ 0.00;$ -0.00"
    End If
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    [B1:D1] = ""
    Range("B1").Select
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]

HOW TO INSTALL Event Code
------------------------------------
If you are new to event code procedures, they are easy to install. To install it, right-click the name tab at the bottom of the worksheet that is to have the functionality to be provided by the event code and select "View Code" from the popup menu that appears. This will open up the code window for that worksheet. Copy/Paste the event code into that code window. That's it... the code will now operate automatically when its particular event procedure is raised by an action you take on the worksheet itself. Note... if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.
Click to expand...
Now i noticed that if i were to change the layout of the cells, the macro changes the layout again? Can i change how the layout looks in the macro? i mean like the profits in column G is coming up as UK pounds, and column C D E and H arent in bold but the others are. Also i noticed that column C where the amount BET is. The numbers are stored as TEXT and is giving errors... is there a way to solve this?
 
Last edited:
Upvote 0
Ramballah said:
Can i change how the layout looks in the macro?
Click to expand...
A macro or, as in this case, event code, can do pretty much anything... as long as the programmer knows what is wanted.



Ramballah said:
...and column C D E and H arent in bold but the others are.
Click to expand...
My code does nothing to bold or not bold any cell values. If you have specific column values that you want bolded, tell me what they are and I'll build it into my code.



I will revise my code again when you tell me about the bolded columns as discussed above, but here is revised code which I think addresses your other questions and makes an addition to the code which I accidentally omitted originally. So, use this code in place of what I gave you earlier and tell me if every (except for the bold question) works as it should...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Application.EnableEvents = False
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "C").Value = Cells(Rw, "C").Value
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    If Cells(Rw, "G").Value = Int(Cells(Rw, "G").Value) Then
      Cells(Rw, "G").NumberFormat = "\$ 0"
    Else
      Cells(Rw, "G").NumberFormat = "\$ 0.00;\$ -0.00"
    End If
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    [B1:D1] = ""
    Range("B1").Select
    Application.EnableEvents = True
  End If
End Sub[/td]
[/tr]
[/table]
 
Upvote 0
Rick Rothstein said:
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Application.EnableEvents = False
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "C").Value = Cells(Rw, "C").Value
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    If Cells(Rw, "G").Value = Int(Cells(Rw, "G").Value) Then
      Cells(Rw, "G").NumberFormat = "\$ 0"
    Else
      Cells(Rw, "G").NumberFormat = "\$ 0.00;\$ -0.00"
    End If
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    [B1:D1] = ""
    Range("B1").Select
    Application.EnableEvents = True
  End If
End Sub[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Hi, This has fixed the error i get on column C but now i get for example 500.00
Since there is no form of cents involved in my coinflips to me this kind of seems annoying. The profits is now indeed in $ instead of UK Pounds which is good.
As for the bolding. I want column C D E and H to be in bold, the others are in bold, but these 4 columns aren't. So if thats possible to solve then thank you very much!
 
Upvote 0
Ramballah said:
Hi, This has fixed the error i get on column C but now i get for example 500.00
Since there is no form of cents involved in my coinflips to me this kind of seems annoying. The profits is now indeed in $ instead of UK Pounds which is good.
As for the bolding. I want column C D E and H to be in bold, the others are in bold, but these 4 columns aren't. So if thats possible to solve then thank you very much!
Click to expand...
Does this handle all of the issues for you...
Code: 
[table="width: 500"]
[tr]
	[td]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Application.EnableEvents = False
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "C").Value = Cells(Rw, "C").Value
    Cells(Rw, "C").NumberFormat = "0"
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    Cells(Rw, "G").NumberFormat = "\$ 0"
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    Cells(Rw, "A").Resize(, 8).Font.Bold = True
    [B1:D1] = ""
    Range("B1").Select
    Application.EnableEvents = True
  End If[/td]
[/tr]
[/table]
End Sub
 
Upvote 0
Rick Rothstein said:
Does this handle all of the issues for you...
Code: 
[TABLE="width: 500"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Rw As Long, MaxWinRow As Long, MaxLoseRow As Long
  If Target.Address(0, 0) = "D1" And Len(Range("D1")) > 0 Then
    Application.EnableEvents = False
    Rw = Cells(Rows.Count, "A").End(xlUp).Row + 1
    MaxWinRow = Evaluate(Replace("MAX(IF((E3:E#=""Win"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    MaxLoseRow = Evaluate(Replace("MAX(IF((E3:E#=""Lose"")*(H3:H#=""Me""),ROW(E3:E#)))", "#", Rw - 1))
    Cells(Rw, "A").Value = Val(Cells(Rw - 1, "A")) + 1
    Cells(Rw, "C").Resize(, 2).Value = Split([SUBSTITUTE(SUBSTITUTE(LOWER(B1),"t","Tails"),"h","Heads")])
    Cells(Rw, "C").Value = Cells(Rw, "C").Value
    Cells(Rw, "C").NumberFormat = "0"
    Cells(Rw, "E").Value = [PROPER(C1)]
    Cells(Rw, "F").Value = [IF(C1="win",IF(RIGHT(B1)="h","Heads","Tails"),IF(RIGHT(B1)="h","Tails","Heads"))]
    Cells(Rw, "G").Value = IIf([D1="me"], IIf(Cells(Rw, "E").Value = "Lose", -1, 1) * Cells(Rw, "c").Value, "")
    Cells(Rw, "G").NumberFormat = "\$ 0"
    Cells(Rw, "H").Value = [PROPER(D1)]
    If [AND(C1 = "win",D1 = "me")] Then
      If MaxWinRow Then
        Cells(Rw, "B").Value = 1 - Cells(MaxWinRow, "B") * (MaxWinRow > MaxLoseRow)
      Else
        Cells(Rw, "B").Value = 1
      End If
    End If
    Cells(Rw, "A").Resize(, 8).Font.Bold = True
    [B1:D1] = ""
    Range("B1").Select
    Application.EnableEvents = True
  End If[/TD]
[/TR]
</tbody>[/TABLE]
End Sub
Click to expand...
I have tried to use this code, but the moment i type in my 100 t for example i get an error: Compile error: Expected End Sub
the last End If is marked in blue so i can retype it and this is marked in yellow:[TABLE="class: cms_table"]
<tbody>[TR]
[TD]Private Sub Worksheet_Change(ByVal Target As Range)[/TD]
[/TR]
</tbody>[/TABLE]

*Edit* It is whenever i make anychange to the worksheet, no matter which cell it is but if i change the contents i will get this error
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

Ramballah
  • Locked
  • Question Question
sort from high to low with VBA
Replies
4
Views
846
Fluff
Fluff
P
  • Solved
Keep W-L of card game played over time
Replies
6
Views
724
Preacherman771
P
D
  • Question Question
Data in cell wrong way round ie "15.22=1|13.33=3" should be "1=15.22|3=13.33" - must finish with data in 1 cell
Replies
3
Views
466
Fluff
Fluff
L
  • Question Question
MSGBOX WHEN A CELL MEETS 2 CONDITIONS
Replies
3
Views
165
dermie_72
D
E
  • Solved
Returning specific texts within a wide range of data coming from the Web
Replies
8
Views
288
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,889
Messages
6,175,223
Members
452,620
Latest member
dsubash

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top