Using the And formula in a VBA macro

[Corinne R]

I'm learning VBA and have got stuck!

I have a spreadsheet with 7 columns of information. I want to work
down the spreadsheet, testing 4 criteria. The critera look at the
columns and test to see if they apply. I can use the And function to
return a true or false value.

I want to use VBA to say that if a particular condition applies then
put 'text1' in the cell to the right, if not test the next condition
and if that is true then put 'text2' in the cell to the right, if not
test the next condition.

I've recorded the macro and am using an activecell.formula = approach.
I just can't get it to run in an Else If way. The first test runs
but the second doesn't. I can't put an if statement before the
activecell.formula.

Has anyone got any ideas on how to overcome this?

Much appreciated


Corinne R

 

[Don Guillett]

one simple way. There is probably a better way if we knew your code and the
full parameters of your need.

if cond1 then activecell.offset(1,2)=text1
if cond2 then activecell.offset(1,2)=text2

 

[Tom Ogilvy]

Dim cell as Range
for each cell in Range("A1:A200")
if cell.Value > 100 then
cell.offset(0,1).Value = "'>100"
elseif cell.Value > 75 then
cell.offset(0,1).Value = "'>75"
elseif cell.value > 50 then
cell.offset(0,1).Value = "'>50"
else
cell.offset(0,1).Value = "'<=50"
end if
Next

 

[Corinne Ross]

Thank you Don

I have managed to get the program to behave in the way I want and
realised where I was going wrong. But there may be a better way to
achieve this and I'd love to learn. It's quite longwinded!

Here's my code:

Dim StudentStatus As String
ActiveCell.FormulaR1C1 = "=AND(RC[-4]=6,RC[-2]=""LW-118"",RC[-3]=21)"
If ActiveCell.Value = "True" Then
StudentStatus = "Prog + NCE"
ActiveCell.Value = StudentStatus
ElseIf ActiveCell.Value = "False" Then
ActiveCell.FormulaR1C1 = "=AND(RC[-4]>3,RC[-4]<6,RC[-2]=""LW-118"")"
If ActiveCell.Value = "True" Then
StudentStatus = "Mop Up"
ActiveCell.Value = StudentStatus
ElseIf ActiveCell.Value = "False" Then
ActiveCell.FormulaR1C1 = "=AND(RC[-4]=6,RC[-3]<21,RC[-2]=""LW-118"")"
If ActiveCell.Value = "True" Then
StudentStatus = "Prog"
ActiveCell.Value = StudentStatus
ElseIf ActiveCell.Value = "False" Then
StudentStatus = "Incomplete"
ActiveCell.Value = StudentStatus
End If
End If
End If
End Sub

 

[Tom Ogilvy]

offset is zero based. to the right would be offset(0,1)

 

[JWolf]

Paste the following formula into E1. If that is not where you
ultimately want it, copy it to the cell you want.
=IF(AND(A1=6,B1=21,C1="LW-118"),"Prog+NCE",IF(AND(A1>3,A1<6,C1="LW-118"),"Mop
Up",IF(AND(A1=6,B1<21,C1="LW-118"),"Prog","Incomplete")))