Solved: VBA formula relating 2 worksheets

[gototcm]

This seems simple but not easy.

I have 2 worksheets WS1 and WS2. For simplicity, WS1 has an "X" in cell a1 or it is blank. WS2 should have a formula in cell b1 that (in Excel) looks like "=if(WS1!a1="X",1,0). But I can't figure out the VBA code to make this happen. I have tried things like

sheets("WS2").activate
Range("a1").Offset(0, 1) = "=if(WS1! & "a1"="X",1,0)" but this is clearly wrong.

Any help is welcome.

[mohanvijay]

try this



Range("a1").Offset(0, 1) = "=if(WS1! & ""a1""=""X"",1,0)"

[mohanvijay]

ignore previous post
try this


Range("a1").Offset(0, 1) ="=if(WS1!a1=""X"",1,0)"

[mikerickson]

Sheets("WS2").Range("a1").Offset(0, 1).FormulaR1C1 = "=--(WS1!RC[-1]=""X"")"

[gototcm]

Thanks, but that didn't seem to work. Here is the complete set of code I tried. When it is executed the error "Run-time Error, 1004" message results.

Sub test()
Sheets("WS2").Select
Range("a1").Offset(0, 1) = "=if(WS1! & ""a1""=""X"",1,0)"
End Sub
:bug:

[Aflatoon]

That is not the code that anyone posted. I would use:
Sub test()
Sheets("WS2").Range("B1").Formula = "=if(WS1!a1=""X"",1,0)"
End Sub

[gototcm]

Aflatoon,

Thanks - that worked. The use of double quotes in VBA is sometimes quite confusing. I appreciate the quick response.

[gototcm]

Sheets("WS2").Range("a1").Offset(0, 1).FormulaR1C1 = "=--(WS1!RC[-1]=""X"")"

Mike,
Thanks. This method works also although it is less intuitive for a newbie than others suggested. But never hurts to learn some new tricks.

[mdmackillop]

Try the Macro Recorder for such. It handles the double quotes etc. Then just substitute the Offset code

Sub Macro1()
'
' Macro1 Macro
'

'
ActiveCell.FormulaR1C1 = "=IF('WS1'!RC[-3]=""X"",1,0)"
End Sub