PDA

View Full Version : VBA: How do I use a Variable Name in a formula



gototcm
01-01-2012, 10:18 PM
I want to use the Bin2Hex method with variable names. Two columns of binary data are in columns C and D in worksheet 2 (W2). The formula will be in a cell a1 on worksheet 1 (W1). The Excel formula is simple:
=(W2!C1 & W2!D1 for the data in row 1. But how do I do this with variables replacing C1 and D1? I've spent hours attempting to sort this.

Here is one method I tried but I get an error

sheets("W2").activate
var1=range("C2")
var2=range("D2")
sheets("W1").activate
Range("a1").Formula = "=bin2hex(W1!"var1 "& W2! "var2")"

The other issue is in var1 and var2, I will have to assign these dynamicaly to different cells. Not quite sure of the format here?

This can't be that hard.

mikerickson
01-01-2012, 11:55 PM
The .Address property has a RelativeTo argument.

Sub test()
Dim RangeForFormula As Range
Dim oneCell As Range, twoCell As Range

Set oneCell = Sheet1.Range("C2")
Set twoCell = Sheet1.Range("D2")
Set RangeForFormula = Sheet2.Range("A2:A15")

With RangeForFormula
.FormulaR1C1 = "=BIN2Hex(" & oneCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & _
"&" & twoCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & ")"
End With
End Sub

Bob Phillips
01-02-2012, 05:01 AM
If you know the cells, why not just use


Worksheets("W1").Range("A1").Formula = "=bin2hex(W2!C2&W2!D2)"

gototcm
01-02-2012, 09:55 AM
Yes, there's the rub. I agree if I knew the cells this is trivial. I have simplified my question from the real application which will have users adding or subtracting rows from sowrksheet 2 (W2) therefore I don't know the cell addresses.

gototcm
01-02-2012, 11:16 AM
This appears to work but I don't understand the syntax of the Rangefor Formula statement

.FormulaR1C1 = "=BIN2Hex(" & oneCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & _
"&" & twoCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & ")"

mikerickson
01-02-2012, 11:31 AM
oneCell.Address(False, True, xlR1C1, True, .Cells(1, 1))

is the address of oneCell with:
relative row reference
absolute column reference
in R1C1 format
with the workbook and sheet specified
the row reference is relative to RangeForFormula.Cells(1,1)

in post #2 that would be "[Workbook1.xlsm]Sheet1!RC3"

gototcm
01-02-2012, 04:25 PM
Thanks again. Two more questions.

1. Why the double ampersand & "&" ?
2. Where is all of this hidden knowledge documented?

thanks for your expertise - it means a lot to me.

mikerickson
01-02-2012, 05:45 PM
The first & is adding the literal "&" to the formula string.
The first & is a VB operator. The second is part of the formula


.FormulaR1C1 = "=BIN2Hex(" & oneCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & _
"&" & twoCell.Address(False, True, xlR1C1, True, .Cells(1, 1)) & ")"
goes to =BIN2Hex(RC3&RC4)

gototcm
01-02-2012, 06:55 PM
OK, I got it. Also found some additional documentation. But have run into more problems.

I'll submit a new thread as this one is getting too long.