Using the And formula in a VBA macro
 

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

Using the And formula in a VBA macro
 

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

--
Don Guillett
SalesAid Software

"Corinne R" wrote in message
om...
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

Using the And formula in a VBA macro
 

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

--
Regards,
Tom Ogilvy

"Corinne R" wrote in message
om...
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

Using the And formula in a VBA macro
 

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Using the And formula in a VBA macro
 

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

--
Regards,
Tom Ogilvy


"Don Guillett" wrote in message
...
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

--
Don Guillett
SalesAid Software

"Corinne R" wrote in message
om...
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

Using the And formula in a VBA macro
 

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(A13,A1<6,C1="LW-118"),"Mop
Up",IF(AND(A1=6,B1<21,C1="LW-118"),"Prog","Incomplete")))

Corinne Ross wrote:

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




*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!