I would like to use the IF Formula to check the contents of a cell and
place a "Yes" if true or nothing if False. The standard formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place the result in a
column. In other words, check A27 and place the result in H1 on
Sheet3. Then check A41 and place the result in H2 and so on. Thanks.
Tom - you've been a huge help!! I unfortunately learn by example - I
wish there was a site or reference materials available that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

Try using the help button.

But the "IF method" is

IF question (range("A27") = 0) then
pass condition (range("A27") = "Yes")
Else
failcondition (nothing)
end if

note: you do not need the else part also when you look up
the "For method" you might like to use the "step" part at
the end.

Jase

The best way to learn syntax is to have a go and try to
debug the error.

-----Original Message-----

I would like to use the IF Formula to check the contents
of a cell and
place a "Yes" if true or nothing if False. The standard
formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the
quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place
the result in a
column. In other words, check A27 and place the result
in H1 on
Sheet3. Then check A41 and place the result in H2 and so
on. Thanks.
Tom - you've been a huge help!! I unfortunately learn by
example - I
wish there was a site or reference materials available
that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Copy tis into a VBA module so you can read it


Sub NS()
Dim R As Long, LR As Long, S3r
'S3r is sheet3 row x
'Application.ScreenUpdating = False 'makes the macro run
faster by not updating video
'you may want to comment out the above line if you want to
watch the macro run
ActiveSheet.Cells.SpecialCells
(xlCellTypeLastCell).Activate 'Finds the last cell in the
WS so the loop knows when to stop
LR = ActiveCell.Row 'Address of the last row in the WS
Range("A1").Select 'Change A1 to the cell address you want
the loop to start at
S3r = 1
Do
If ActiveCell.Value = 0 And ActiveCell.Value < "" Then
Worksheets("Sheet3").Range("H" & S3r).Value = "Yes" 'put
the value "yes" in cloumn H row x
ActiveCell.Offset(14).Activate 'moves the cursor (active
cell) 14 rows down
R = ActiveCell.Row 'Sets R = to the current row so we can
test if the loop should continue
S3r = S3r + 1
Else
ActiveCell.Offset(14).Activate 'moves the cursor (active
cell) 14 rows down
R = ActiveCell.Row
End If
Loop Until R = LR Or R LR
End Sub

Marty

-----Original Message-----

I would like to use the IF Formula to check the contents
of a cell and
place a "Yes" if true or nothing if False. The standard
formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the
quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place
the result in a
column. In other words, check A27 and place the result
in H1 on
Sheet3. Then check A41 and place the result in H2 and so
on. Thanks.
Tom - you've been a huge help!! I unfortunately learn by
example - I
wish there was a site or reference materials available
that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

It rather depends on (a) you want the answer to change if
the data changes ie you want the formula in the cell, or
(b) you want VBA to enter the result

In the example, rw and cl are lomg and represent the
address of the cell to be tested. "B5" then is rw=5, cl=2
and the answer is placed one column to the right

(a) place a formula:-

Cells(rw, cl + 1).FormulaR1C1 = _
"=IF(RC[-1]=0,""Yes"","""")"

(b) use VBA to set the answer:-
If Cells(rw, cl).Value = 0 Then
Cells(rw, cl + 1) = "Yes"
End If


Patrick Molloy
Microsoft Excel MVP








-----Original Message-----

I would like to use the IF Formula to check the contents
of a cell and
place a "Yes" if true or nothing if False. The standard
formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the
quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place
the result in a
column. In other words, check A27 and place the result
in H1 on
Sheet3. Then check A41 and place the result in H2 and
so on. Thanks.
Tom - you've been a huge help!! I unfortunately learn
by example - I
wish there was a site or reference materials available
that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

.

Try "Immediate If"
MyValue = IIF(A27=0,"Yes","No")

Regards
BrianB
=============================================

Novice wrote in message ...
I would like to use the IF Formula to check the contents of a cell and
place a "Yes" if true or nothing if False. The standard formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place the result in a
column. In other words, check A27 and place the result in H1 on
Sheet3. Then check A41 and place the result in H2 and so on. Thanks.
Tom - you've been a huge help!! I unfortunately learn by example - I
wish there was a site or reference materials available that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

"Novice" wrote in message
...

I would like to use the IF Formula to check the contents of a cell and
place a "Yes" if true or nothing if False. The standard formula of IF
(A27=0,"Yes","") doesn't work in VBA because of the quotation marks for
some reason. How do I do it?

Also, I would like to counter the rows by 14 and place the result in a
column. In other words, check A27 and place the result in H1 on
Sheet3. Then check A41 and place the result in H2 and so on. Thanks.
Tom - you've been a huge help!! I unfortunately learn by example - I
wish there was a site or reference materials available that catered to
the very beginner! Thanks again!!


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/


If you want to use VB to put a formula in a cell, the formula must be
surrounded by double quotes since it is text. Since the IF requires double
quotes within the formula, you need to use double double quotes. Maybe this
example makes more sense:

Range("A28").formula = "=if(A27=0,""Yes"","""") "

--Kiloran