Thread: VBA to automatically enter values from another cell

Maybe i am confused but why don't you just use a formula?

So using your attached worksheet, Sheet 1 Cell X14 would have this put in it.

=$K$2

You would then fill down to X17. Do this for each one. Should only take a minute.

Originally Posted by stnkynts

Maybe i am confused but why don't you just use a formula?

So using your attached worksheet, Sheet 1 Cell X14 would have this put in it.

=$K$2

You would then fill down to X17. Do this for each one. Should only take a minute.

Its just that I attached a small example. In reality this worksheet is going to have hundreds and hundreds of these races day and would be too much work going through them each day.Sorry i should have mentioned this was just a sample of the actual sheet I use.

Thanks
Gemma

Just to add I do have a similar sheet that looks at each set of races and automatically enters the jockey rankings from 1 to 34. I would imagine it would be using this type of code but changing it around.

Sub RankGroups()
Dim RNGs As Range
Dim Grp As Long
Dim First As String

Set RNGs = Range("y:y").SpecialCells(xlCellTypeFormulas, xlNumbers)

For Grp = 1 To RNGs.Areas.Count
With RNGs.Areas(Grp)
First = Replace(.Cells(1).Address, "$", "")
.Offset(0, 1).Formula = "=RANK(" & First & "," & .Address & ",1)"
End With
Next Grp

End Sub

Try this:

Sub PopulateEntryFee()
Dim myRange As Range
Dim iGroup As Long

Set myRange = ActiveSheet.Range("w:w").SpecialCells(xlCellTypeConstants, xlNumbers)

For iGroup = 1 To myRange.Areas.Count
    With myRange.Areas(iGroup)
        .Offset(0, 1).Value = .Offset(-12, -12).Value
        .Offset(0, 1).FillDown
    End With
Next iGroup

End Sub

This is exactly what I was looking for, thank you very much!

Gemma

option without specialcells, run code "test"

Thanks Watersev, this also works and it is great to see different styles when setting vba's up.

I do actually have a similar question to the one solved, but never posted originally as I wasn't sure it was possible for the previous vba to be set up. It is similar to the one above, except for it is looking for the stakes returns of each race and only allocates a value to the first 4 horses in each race. I have attached an updated sheet if anyone has time to look at it.

Thanks again to all who have viewed and commented. My hope is that through learning here I will eventually be able to answer some questions myself rather than be the one asking them.

Gemma

Here is my solution. I can't help you with watersev's version as his way is usually like a foreign language to me.

Sub UpdateStakes()
Dim myRange As Range
Dim iGroup As Long

Set myRange = ActiveSheet.Range("w:w").SpecialCells(xlCellTypeConstants, xlNumbers)

For iGroup = 1 To myRange.Areas.Count
    With myRange.Areas(iGroup).Resize(4, 1)
        .Offset(0, 2).Value = .Offset(-12, -11).Value
        .Offset(0, 2).FillDown
    End With
    With myRange.Areas(iGroup)
        .Cells(1, 3).Value = .Cells(1, 3).Value * 0.5
        .Cells(2, 3).Value = .Cells(2, 3).Value * 0.25
        .Cells(3, 3).Value = .Cells(3, 3).Value * 0.15
        .Cells(4, 3).Value = .Cells(4, 3).Value * 0.1
    End With
Next iGroup

End Sub