VBA Excel trying to get a variable defined as Range into Cells format

Question

I'm probably missing something very simple here but, I have cobbled together the following code VBA code in Excel,, mainly from answers I've seen here and this runs OK. However what I would like to do, is have the Cells(3, 3) be replaced by startlocation.Address but no matter how I try to arrange it I keep get an error message. So, is there a way of converting startlocation.Address into the Cells( ) format?

Public Sub Button1_Click()

    Dim buttonName As String
    Dim buttonCell As Range
    Dim startlocation As Range
    Dim qtyvalue As Long
    Dim stepcount As Long
    
    buttonName = Application.Caller
    Set buttonCell = ActiveSheet.Shapes(buttonName).TopLeftCell
    MsgBox buttonCell.Address ' display the address of the button, which in this case is D1

    Set startlocation = buttonCell.Offset(2, -3) ' from the postion of the button, move to the cell (here it's A3) with the first piece of wanted data
    MsgBox startlocation ' display value within cell A3, which in this case is 89
    MsgBox startlocation.Address 'show the address of startlocation which in this case is A3

    ' on Master sheet cell (89,16) put the value of cell (3,3), which in this case is 4
    Sheets("Master").Cells(startlocation, 16).Value = Sheets("Menu2").Cells(3, 3).Value

I'm trying to get contents of C3 to be copied to P89 (the 89 is derived from A3) on master sheet.

Try ... = Sheets("Menu2").Range(startlocation.Address).Value not too sure exactly which part is the problem. Might help to explain in simple words what the code's intended purpose is. Which sheet is the button located on? — Tim Williams
– Tim Williams, Commented Nov 12 at 23:14
startlocation is a Range object, but which cell are you trying to reference with Cells(startlocation, 16)? — motosann
– motosann, Commented Nov 13 at 0:40
Thanks Tim, that runs but doesn't quite get the result I want. Two sheets in SS. The button in 2nd (Menu2). Button address is noted, then offset by fixed amount, and this cell value is the index number for row position in Master sheet (89 in this test). Then want contents of C3 on Menu2 sheet to go to P89 on Master sheet. Your suggestion put 89 into P89, Hard to explain without a picture, which I don't think can be done here — N. Blain
– N. Blain, Commented Nov 13 at 0:48
motosam trying to get contents of C3 on Menu1 sheet to P89 on Master sheet — N. Blain
– N. Blain, Commented Nov 13 at 1:01
The startlocation in Cells(startlocation, 16) should be a number, not a Range object. For example, use startlocation.Value — motosann
– motosann, Commented Nov 13 at 2:00

VBasic2008 · Accepted Answer · 2025-11-13 07:23:51Z

2

Copy Values

Public Sub Button1_Click()

    Const S_ID_COLUMN As String = "A"
    Const S_QTY_COLUMN As String = "C"
    Const S_ROW_OFFSET As Long = 2
    Const D_SHEET_NAME As String = "Master"
    Const D_QTY_COLUMN As String = "P"
    
    Dim ButtonName As Variant: ButtonName = Application.Caller
    
    If IsError(ButtonName) Then
        MsgBox "Click on a button to run!", vbExclamation
        Exit Sub
    End If
    
    Dim sws As Worksheet: Set sws = ActiveSheet
    
    Dim sRow As Long: sRow = _
        sws.Shapes(ButtonName).TopLeftCell.Row + S_ROW_OFFSET
    Dim dRow As Long: dRow = sws.Cells(sRow, S_ID_COLUMN).Value
    
    Dim dws As Worksheet: Set dws = sws.Parent.Sheets(D_SHEET_NAME)
    
    dws.Cells(dRow, D_QTY_COLUMN).Value = sws.Cells(sRow, S_QTY_COLUMN).Value

End Sub

answered Nov 13 at 7:23

VBasic2008

56.9k5 gold badges21 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

N. Blain Nov 13 at 8:14

Many thanks, that worked. Being a complete novice I'll now spend a bit of time working it out.

Tim Williams · Accepted Answer · 2025-11-13 04:14:14Z

1

Try this - if I understand what you want...

Public Sub Button1_Click()

    Dim buttonName As String
    Dim buttonCell As Range, idCell As Range
    Dim qtyvalue As Long
    
    buttonName = Application.Caller
    Set buttonCell = ActiveSheet.Shapes(buttonName).TopLeftCell
    Set idCell = buttonCell.Offset(2, -3)
    qtyvalue = idCell.Offset(0, 2).Value  'read the quantity
    
    Sheets("Master").cells(idCell.Value, 16).Value = qtyvalue
    
End Sub

answered Nov 13 at 4:14

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

4 Comments

N. Blain Nov 13 at 7:58

Brilliant, thank you Tim, that worked. Much more elegant than what I had put together. Being very new to this, will take me a while to figure it all out, but I can adapt this to do the whole job.

Tim Williams Nov 13 at 18:19

Did you also want to process other items in the same list (id=33 and id=44)?

N. Blain Nov 18 at 20:00

Thanks, yes, but I'm OK looking after that

Tim Williams Nov 17 at 18:13

Please flag as "Accepted" if this answered your question, to help out anyone coming along later with a similar problem.

Collectives™ on Stack Overflow

VBA Excel trying to get a variable defined as Range into Cells format

2 Answers 2

Copy Values

1 Comment

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Copy Values

1 Comment

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related