Loop through cells and place specific values onto them

Question

So I want to create a table like this

I want those specific numbers on the cells.

I had an idea of how to do it where I check the value of the cell above the one I'm filling in.

Something like this

if cell_above.value = 2 then
current_cell.value = -2

if cell_above.value = -2 then
current_cell.value = 4

and on and on like that.

This just seems like a lot of work and there is probably an easier way to do this. Can somebody help me think of another way to solve this? Thank you

@SJR This is part of a much bigger macro I'm creating so I would have to do this on VBA. And no, it isn't really a pattern, I just need those specific values to be on my cells. — Sam, Feb 11 '22 at 13:25
If it is not a pattern, where does the 3 comes from? Still not clear what you are really asking — FunThomas, Feb 11 '22 at 13:28
@FunThomas It is just the values I am using. I am creating a timeline and this decides the height of where the values are placed in the timeline. I just know that these numbers work well and usually help to not have the data labels crashing with each other when they are displayed. — Sam, Feb 11 '22 at 13:28
But where does it end? I really think this is something you could make a stab at yourself. — SJR, Feb 11 '22 at 13:29
If there is no pattern just write the values into an array and dump it to the range. — Warcupine, Feb 11 '22 at 13:31
I would know where it ends on my actual macro, this is just a random example and I would like it to end on row 12 like that — Sam, Feb 11 '22 at 13:31
@earthguestg that's exactly what I was looking for! if you submit it as an answer I can select it as the answer that solved my question. — Sam, Feb 11 '22 at 13:44

score 0 · Answer 1 · answered Feb 11 '22 at 13:44

Still not clear what your problem is. Define a const Array of values and write them to the sheet. Following three variations to get you the idea:

Sub fillMe1()
    Dim MyValues
    MyValues = Array(2, -2, 4, -4, 6, -6, 3, -3, 5, -5, 7, -7)
    With ThisWorkbook.Sheets(1)
        .Range("A1").Resize(UBound(MyValues) + 1, 1).Value = Application.Transpose(MyValues)
    End With
End Sub

Sub fillMe2()
    Dim MyValues
    MyValues = Array(2, -2, 4, -4, 6, -6, 3, -3, 5, -5, 7, -7)
    Dim i As Long
    
    For i = 0 To UBound(MyValues)
        With ThisWorkbook.Sheets(1)
            .Range("B1").Offset(i, 0).Value = MyValues(i)
        End With
    Next i
End Sub

Sub fillMe3()
    Dim MyValues
    MyValues = Array(2, 4, 6, 3, 5, 7)
    Dim i As Long
    
    For i = 0 To UBound(MyValues)
        With ThisWorkbook.Sheets(1)
            .Range("C1").Offset(i * 2, 0).Value = MyValues(i)
            .Range("C1").Offset(i * 2 + 1, 0).Value = MyValues(i) * -1
        End With
    Next i
End Sub

Loop through cells and place specific values onto them

1 Answers1