VBA if statement help

Dell INSPIRON 6000
January 6, 2009 at 07:20:04
Specs: Windows Vista, pentium 4 processor
Hi i neeed some help
im trying to do an if statement in excel but i need to make if so it recognises it in every cell in column BO and BP without having to type in the code for every single option

for example if bo2 = yes then bp2 = no

ive got this but it doesnt work for every cell

Private Sub CommandButton1_Click()
If Range("bo2") = "yes" Then
Range("bp2") = "nope"
If Range("bo2") = "lion" Then
Range("bp2") = "tiger"
End If
End If
If Range("bo3") = "no" Then
Range("bp3") = "yh"
End If
End Sub

please help anyone ?

See More: VBA if statement help

Report •

January 6, 2009 at 08:32:14
This code should do what you want. It uses a for ... next loop and also uses a select case statement. Both are really handy for doing repetitive tasks!

Note I've used LCase() for when I'm writing the variable to make it work for "Lion", "LION", "lioN" etc.

sub loopthrough()

for i = 2 to 100 '100 is the last row

vartest = LCase(sheet1.cells(i, 67).value)

Select case vartest
case "lion"
varresult = "Tiger"
case "yes"
varresult = "no"
case "another thing"
varresult = "good"
case else
varresult = "unknown"
end select

Sheet1.cells(i, 68).formula = varresult

Next i

end sub

Report •

January 6, 2009 at 08:46:52
Thanxs for this
how does it know which cell to put it in ?
can this be activated when a button is pressed

Report •

January 6, 2009 at 09:26:11
cells(i, 67) is a way of writing "BO"i (where i is a number between 2 and 100)

cells(i, 68) is therefore a way of writing "BP"i (where i is a number between 2 and 100)

Yes it can be assigned to a button. On excel 2003, go to Tools/Customise...

On the "Commands" tab, go down to macros, drag "custom button" up to the toolbar. Click on it once you've closed that, and you can assign the macro to it.

It won't work in another workbook though. In fact I can't remember how to make it appear in only one workbook. I never use them, prefer to use Alt-F8. DerbyDad will know I'm pretty sure!

Report •

Related Solutions

January 6, 2009 at 10:10:00
Thankyou for the help i think i can edit it to what i want now :)

Report •

Ask Question