Excel VBA Button - Fast way to change reference cell

Question

Uh hello stackoverflow, I just started learning excel VBA for works and I find a problem that is I have to manually change the reference cell of a button manually for 500 buttons I wanted to make. For simplification sake:

Say in Column A I have several information normally hidden.

I put a button in each cell of Column B which will display the information in Column Ax (x being the cell number) to Cell Cx. Say:

Range("C1").Value = Range("A1").Value

The button I put in B1 would do the above, that is showing the value of C1 to be what is put in A1.

Now, I have 500 rows of data like this. Is it possible to make a dynamic buttons that knows the cell it is positioned in, and when moved/copy pasted to another cell, refer to its new location to dynamically change the reference cell in its code (Ax and Cx in that example)? So if I put the button in cel B67, the reference cell in the button code would change to C67 and A67 respectively.

Answer 1

_{[Before answering, here's my take on the things:]}

I think the question you're trying to ask here is "how to find the last non-empty row". The reason why I'm not flagging it as one is because, you may not be aware that you actually should be asking that question.

In general (and without meaning to sound condescending) you probably should study up on the programming basics first before asking questions like this here. Take some basic course somewhere (there are many) and only then begin experiment with some coding, because if you're missing coding fundamentals, then you'll never understand the bigger pictue behind it

---

What you're trying to describe here is a basic loop in programming language. How does the loop work? Let's pretend that we're a computer and we could also speak (in human language, not binary 1s and 0es).

1. I want to go through all the cells in a dynamic range
2. I need to know where does the dynamic range start and end (fetch me ending point)
3. I'll begin at starting point and end at ending point (duhh, silly humans should know that...)
4. For each and every cell in the range I'll do a certain action.
5. And that's it, fairly easy. Was a matter of seconds.

If we were trying to exactly represent what I described here:

Option Explicit ' < prevents you from typos, undeclared variables and so on...
Private Sub eachActiveCell()
   Dim actionrange as Range
   Dim lastrow as Long

   lastrow = Sheets("Your Sheet Name").Cells(Rows.Count, "A").End(xlUp).Row 
   'finds last (active) row ^
   Set actionrange = Sheets("Your Sheet Name").Range(Cells(1, "A"), Cells(lastrow, "A")) 
   ' this sets our range to (A1:A <lastrow>) ^

   Dim cell as Range
   For each cell in actionrange
       cell.Offset(0, 2) = cell.Value2 ' utilizes offset, A1 offset by 2 columns = C1
   Next cell

End Sub

Other alternative, as it may be easier to comprehend with rows, rather than Ranges.

Option Explicit
Private Sub foriinA()

    Dim ws as WorkSheet: Set ws = Sheets("Your Sheet Name")
    Dim lastrow As Long
    lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    Dim i As Long
    For i = 1 To lastrow
        ws.Cells(i, "C") = ws.Cells(i, "A")
    Next i

End Sub