VBA To Return Mixed Formula of Literals and Cell Addresses

[wsnyder]

Hi all,

Using Excel 365.

How can I get VBA to return a Formula that is a combination of Literals and Cell Refrences so that the formula will update based on the values of the cell references?
I tried a few variations of

Frmla = "=" & SupplierName _
& ""ColNbr_X"" & ""RowNbrDataBegin"" _
& SupplierNbr _
& ""ColNbr_Y"" & ""RowNbrDataBegin""

But I am getting the items in quotes as literals instead of a cell reference such as D6

Thanks
-w

 

[Jeffrey Mahoney]

If you are trying to concatenate all of the values from those cells, try this:

Sub Hey()
  Dim Frmla As String
  Dim Q As String
  
  Q = Chr(34) & "," & Chr(34)
  
  'No spaces or delineation
  Frmla = "=SupplierName&ColNbr_X&RowNbrDataBegin&SupplierNbr&ColNbr_Y&RowNbrDataBegin"
  
  'Comma delineation
  Frmla = "=SupplierName&" & Q & "&ColNbr_X&" & Q & "&RowNbrDataBegin&" & Q & "&SupplierNbr&ColNbr_Y&" & Q & "&RowNbrDataBegin"
  ActiveCell.Value = Frmla
  
  
End Sub

 

[wsnyder]

Thanks Jeffrey,

I ended up switching all 4 components to cell addresses.
This is my final formula with your good help.

frmla = "=" & Vendor_Name_Address _
                    & "&" & Order_Number_Address _
                    & "&" & PO_Nbr_Address _
                    & "&" & Acct_Nbr_Address

Thanks,
-w