change formula to code function

  • Thread starter Thread starter Corey
  • Start date Start date
C

Corey

=IF(AND('Sheet One'!F16<>""),'Sheet One'!F16+'Sheet Two'!F16,"")

The above formula is what i want to change to a code function.

I am not quite sure how to write this, especially when working with
references across different worksheets.

Any help appreciated

Regards

Corey
 
Coey,

You have to refer to the worksheets and the ranges as

worksheets("Sheet One").Range("F16")
and
worksheets("Sheet Two").Range("F16")

therefore the formual is

if worksheets("Sheet One").Range("F16") <> "" then
worksheets("???").Range("???") = _
worksheets("Sheet One").Range("F16") + _
worksheets("Sheet Two").Range("F16")
else
worksheets("???").Range("???") = ""
endif
' replace the ??? with the worksheet and cell where the ans goes.


it can also be done using a iif which is like the if in excel:

worksheets("???").Range("???") = _
iif(worksheets("Sheet One").Range("F16") <> "", _
worksheets("Sheet One").Range("F16") + _
worksheets("Sheet Two").Range("F16"), _
"")

If you are using the references to worksheets a lot make a reference to the
worksheet:

dim ws1 as worksheet
dim ws2 as worksheet

set ws1= worksheets("Sheet One")
set ws2= worksheets("Sheet Two")
set wsans=worksheets("???")

wsans.range("???") = iif(ws1.Range("F16") <> "", _
ws1.Range("F16") + ws2.Range("F16"), "")
 
Your problem is with the AND that is not used.
Here it is as code:
Sub test()
Dim a As String
a = Chr(34)
Worksheets(2).Select
Cells(1, 1) = "=IF('Sheet One'!F16<>" & a & a & ",'Shee
One'!F16+'Sheet Two'!F16," & a & a & ")"
End Su
 
Back
Top