Build VBA if satements or taking suggestions.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Todd F. - 20 Mar 2006 16:07 GMT

I need to learn about vba IF Statements so here is an eample of what I do and
amy I ask the best way to do it in excel

I have in column B different metrics, I need to read what they are and place
a result in another column say C. I do this many times so if I could see how
to go in and change the fields that would be great.

If column B says “ Online technical Assistance” or “Technical Assistance
Center” then “TACO”

Else if column B says” Product Service Specialists” then “PSS”

I am use to doing this with if statements in a report writer with no IF
limits but if there is a better way to do this in excel vba then with IF then
I am listening.

If we stick with if’s who many IF else or IF or’s can I have ( ref. the 7 if
limit in excel)

Thanks for your time

Reply to this Message

Tom Ogilvy - 20 Mar 2006 17:00 GMT

Sub ProcessData()
Dim col As Long, rng As Range
Dim cell As Range, sStr As String
col = 2 ' "B"
Set rng = Range(Cells(2, col), Cells(2, col).End(xlDown))
For Each cell In rng
Select Case True
Case InStr(1, cell, "Online technical Assistance", vbTextCompare) > 0 Or _
InStr(1, cell, "Technical Assistance Center") > 0
sStr = "TACO"
Case InStr(1, cell, "Product Service Specialists") > 0
sStr = "PSS"
Case Else
sStr = "Not identified"
End Select
cell.Offset(0, 1).Value = sStr
Next

End Sub

You can have as many cases as you want as far as I know.

You might also explore a Lookup table on another sheet and using a lookup
worksheet function.

Signature

Regards,
Tom Ogilvy

> I need to learn about vba IF Statements so here is an eample of what I do and
> amy I ask the best way to do it in excel
[quoted text clipped - 16 lines]
>
> Thanks for your time

Reply to this Message

Todd F. - 20 Mar 2006 17:46 GMT

I appreciate the time Tom. thiusd thing works great.

I use the "cell.Offset(o,1).vlue = sStr" to dictate which column the result
goes in- this macro was reading B and Placing in C

I also get that I can change "2" (B) to what ever then offset will start
from new col position & not col 2

Question How does this macro know where to start reading verticaly and
placing result in right row ( strarting)- I usually have titles in row 1 and
no named ranges but curious in how it is thinking

What if I want it to start reading under a title in row 4 - not likely but
try to learn how this thing works.

again thank you

> Sub ProcessData()
> Dim col As Long, rng As Range
[quoted text clipped - 41 lines]
> >
> > Thanks for your time

Reply to this Message

Tom Ogilvy - 20 Mar 2006 18:04 GMT

Set rng = Range(Cells(2, col), Cells(2, col).End(xlDown))

in that line, the "2's" indicate start in row 2.

Signature

Regards,
Tom Ogilvy

> I appreciate the time Tom. thiusd thing works great.
>
[quoted text clipped - 58 lines]
> > >
> > > Thanks for your time

Reply to this Message

Todd F. - 20 Mar 2006 18:10 GMT

thanks - let me mess with this awhile

> Set rng = Range(Cells(2, col), Cells(2, col).End(xlDown))
>
[quoted text clipped - 62 lines]
> > > >
> > > > Thanks for your time

Reply to this Message

Tom Ogilvy - 20 Mar 2006 17:18 GMT

Signature

Regards,
Tom Ogilvy

> I need to learn about vba IF Statements so here is an eample of what I do and
> amy I ask the best way to do it in excel
[quoted text clipped - 16 lines]
>
> Thanks for your time

Reply to this Message