Using IF Formula

  • Thread starter Thread starter Novice
  • Start date Start date
N

Novice

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!!
 
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.
 
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
 
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
 
Try "Immediate If"
MyValue = IIF(A27=0,"Yes","No")

Regards
BrianB
=============================================
 
Novice said:
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!!
Click to expand...

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
 
Back
Top