PDA

View Full Version : Select Case Function



Daxton A.
07-23-2012, 05:12 PM
I am a little rusty with VBA but here goes,
I am wanting to create a new phonebook. Now in my counting module I
currently have If statements to check for any text written in cell A thru AE.
I recently read that a Select Case would work better but I have no idea how to make a Select Case work. Can someone please help.

Thx!
Daxton

Currently:
'Count the contacts
'========================================
Do While Range("A" & i) <> ""

cellText = i - 1

If Range("A" & i) = "" Then
cellText = i
ElseIf Range("B" & i) = "" Then
cellText = i
ElseIf Range("C" & i) = "" Then
cellText = i
ElseIf Range("D" & i) = "" Then
cellText = i
ElseIf Range("E" & i) = "" Then
cellText = i
ElseIf Range("F" & i) = "" Then
cellText = i
ElseIf Range("G" & i) = "" Then
cellText = i
ElseIf Range("H" & i) = "" Then
cellText = i
ElseIf Range("I" & i) = "" Then
cellText = i
ElseIf Range("J" & i) = "" Then
cellText = i
ElseIf Range("K" & i) = "" Then
cellText = i
ElseIf Range("L" & i) = "" Then
cellText = i
ElseIf Range("M" & i) = "" Then
cellText = i
ElseIf Range("N" & i) = "" Then
cellText = i
ElseIf Range("O" & i) = "" Then
cellText = i
ElseIf Range("P" & i) = "" Then
cellText = i
ElseIf Range("Q" & i) = "" Then
cellText = i
ElseIf Range("R" & i) = "" Then
cellText = i
ElseIf Range("S" & i) = "" Then
cellText = i
ElseIf Range("T" & i) = "" Then
cellText = i
ElseIf Range("U" & i) = "" Then
cellText = i
ElseIf Range("V" & i) = "" Then
cellText = i
ElseIf Range("W" & i) = "" Then
cellText = i
ElseIf Range("X" & i) = "" Then
cellText = i
ElseIf Range("Y" & i) = "" Then
cellText = i
ElseIf Range("Z" & i) = "" Then
cellText = i
ElseIf Range("AA" & i) = "" Then
cellText = i
ElseIf Range("AB" & i) = "" Then
cellText = i
ElseIf Range("AC" & i) = "" Then
cellText = i
ElseIf Range("AD" & i) = "" Then
cellText = i
ElseIf Range("AE" & i) = "" Then
cellText = i
Else
i = i + 1
End If




Loop

patel
07-23-2012, 10:40 PM
your way is totally wrong, explain us what is your goal.
can you attach a sample ?

Bob Phillips
07-24-2012, 12:00 AM
As patel says, that code is very odd. It sets a value, and updates that if there is a blank in the range, then does nothing with it.

I cannot see a Select Case being any better, but I can see ways of simplifying and improving upon that code, but again, it would be better to know exactly what you are trying to do, as I think you are not asking the real question.