Bin2Hex Method between Worksheets

[gototcm]

I want to use the bin2hex method between 2 worksheets, W1 and W2.

W2 contains 25 rows of 2 binary digits each in columns C and D. C1-D1 may contain 0,1, and C2-D2 may contain 1,1 for example.

W1, column A and 25 rows, is to contain the function =Bin2Hex(W1!C1 & W2!D1) for each of the 25 corresponding rows in W2 but where C and D are variables.

I have tried some VBA code within a For loop using i as a row count which didn’t work so just went back to doing one entry in cell a1 of W1 such as

Range("a1").Formula = "=bin2hex(W2!(C & 1) & data!W2 (D&1))" and variants such as
Range("a1").Formula = "=bin2hex(W2!(“”C”” & “”1””) & data!W2 (“”D””&””1””))"

And other iterations, none of which work leading to the Run-time error 1004, Object-Defined error. Any ideas and also where are the rules documenting how to deal with this?

Thanks

[mikerickson]

Try this
[VBA]With Worksheets("ws1").Range("A1:A25")
.FormulaR1C1 = "=BIN2HEX(WS2!RC[+2] & WS2!RC[+3])"
End With[/VBA]

[gototcm]

Thanks much Mike. I rarely use R1C1 coding but I can see why this comes in handy. One thing that isn't clear is that in WS2 is it implied that the references [+2],[-1], etc. are always from [R1C1]? It begs the question of how to establish a different base address.

Need to go back and read some tutorials to clear my head as I continue to learn new tricks.

Tom