VBA: How do I use a Variable Name in a formula

[gototcm]

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

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

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]

The .Address property has a RelativeTo argument.

[VBA]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
[/VBA]

[Bob Phillips]

If you know the cells, why not just use

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

[gototcm]

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]

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]

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]

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]

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]

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.