Copy Changing Cell Value to New Cell

[Juiced91]

Hey Guys

This is the proble i have at the moment i created a mini game that has a cell that changes when the macro is run.

I would like to copy the cell value when it has changed to a new cell.

So for instance:

A17 = W Then C1 = W
A17 = L Then C2 = L
A17 = L Then C3 = L

A17 is changing via a formula based on many other formulas and their results so its not a direct change by myself.

I tried to implement this macro:

Sub Macro
Dim i As Long


  For i = 1 To 2000
        Range("A1").Calculate
        Range("B1").Offset(i - 1).Value2 = Range("A1").Value2
   Next i



End Sub

But it Just copied A17 2000 times which is obviously not what i want.

My Main Macro is:

Sub Poker_Coll()
     'Uses a collection not a dictionary
    Dim NumCards As Integer, Players As Integer
    Dim Suits(), Cards()
    Dim J As Variant, K As Variant
    Dim CardNum As Integer, i As Integer, v As Integer, CardPick As Integer
    Dim Casino As Collection, CardName As String
    Dim NewSheet   As Worksheet
     
     
    Set Casino = New Collection
     ' number of cards
    NumCards = 3
     ' number of players
    Players = 2
     
    If NumCards * Players > 52 Then
        MsgBox "You have exceeded one deck!", vbCritical
        Exit Sub
    End If
     
    Application.ScreenUpdating = False
     
     'Add a new sheet for the game
    'Set NewSheet = ActiveWorkbook.Sheets.Add
     
     'Requires Excel 2000+ to use Array
    Suits = Array("", "", "", "")
    Cards = Array("1", "2", "3", "4", "5", "6", "7", "8", "9", _
    "0", "Jack", "Queen", "King")
     
     ' Add the cards to the Collection
    i = 1
    For Each J In Suits
        For Each K In Cards
            Casino.Add K & "" & J
            i = i + 1
        Next K
    Next J
     
     'Pick a random card, deal it and remove it from the pack
    For i = 1 To Players
        ActiveSheet.Cells(1, i) = "Player " & i
        For v = 1 To NumCards
            CardPick = Int(Rnd() * Casino.Count + 1)
            CardName = Casino(CardPick)
            ActiveSheet.Cells(v + 1, i) = CardName
            Casino.Remove (CardPick)
        Next v
    Next i
     
     'dump undealt cards
    v = 1
    ActiveSheet.Cells(v, i + 1) = "Undealt Cards"
    For Each J In Casino
        v = v + 1
        ActiveSheet.Cells(v, i + 1) = J
    Next J
     
    ActiveSheet.UsedRange.EntireColumn.AutoFit
     
    Application.ScreenUpdating = True
     
    Set Casino = Nothing
     
End Sub

I dont know much about VBA infact very little and all my code was found online that i just edited for my needs.

Thanks alot for any help

Edit* if i need to attach my spreadsheet i can as the formulas i have are not located in the VBA but done manually on the sheet itslef

 

[Juiced91]

Anybody?

 

[Snakehips]

Juiced91,

Welcome to Mr Excel.

I'm not entirely sure that I understand what you are wanting.
However, if it is that you wish to have the next cell in column B take the value of A1 ONLY if and when A1 value changes then try this.
The only thing is that the list ib B will start at B2.
Open the VB editor, select the required sheet module and paste in the below code.

Private Sub Worksheet_Change(ByVal Target As Range)
LastB = Cells(Rows.Count, 2).End(xlUp).Row
If Not Range("B" & LastB) = Range("A1") Then Range("B" & LastB + 1) = Range("A1")
End Sub

Hope that helps.

 

[Juiced91]

Thanks alot for the reply i will go and try it but let me also try and explain better.

Lets just make it A1 so that its easier to understand.

A1 will get a value inserted into it (a letter that is either W/L/T) based on a specific result that occurs in my spreadsheet.

So cell A1 will have a value of W.

I want to copy this value into Column B as it changes.

So A1 is always the main value that needs to be stored inside column B.

A1 = W now i want to copy W to B1
A1 = W now i want to copy W to B2
A1 = T now i want to copy T to B3
A1 = W now i want to copy W to B4

So as you can see as the A1 value changes i want to copy its value and move 1 row down in the column.

Hope it makes more sense now?

 

[Juiced91]

Your code works great HOWEVER, my cell has a forumla in it for letters and so it gets confused how would i copy the letter only?

 

[Snakehips]

JUiced91,

When you say your cell has a formula, I assume that you mean cell A1 ??
That should not be a problem with the code I gave you, (other than it starts from B2), nor the fact that A1 will contain letters.
So A1 is always the main value that needs to be stored inside column B.

A1 = W now i want to copy W to B1
A1 = W now i want to copy W to B2
A1 = T now i want to copy T to B3
A1 = W now i want to copy W to B4

So as you can see as the A1 value changes i want to copy its value and move 1 row down in the column

You can't however use that code to record W to B1 then W to B2 as above because there is no change in value of A1.
To do that you must identify some other event to trigger updating B