Multiple condition formatting

**Kb24** · 09-10-2009, 06:24 AM

Hi.. got a simple question here...
I need to change the cells in a column but with multiple conditions e.g :


Column A1

3
4
5
7
8
9

Into


Column A1

6
43
2
1
11
3

Or in select case function it would look like this (sort of) :

Select case 
Case 3 
cell = 6
Case 4 
cell = 43
Case 5 
cell = 2
Case 7 
cell = 1
Case 8
cell = 11
Case 9
cell = 3

Ive been trying to do this but can;t seem to get it to work ..

Sub Change()
Set Beta = Range("A1:A7")
    For Each cell In Beta
    
        If cell.Text = "0003" Then
            cell.Text = "0006"
        ElseIf cell.Text = "0004" Then
            cell.Text = "0043"
        ElseIf cell.Text = "0005" Then
            cell.Text = "0002"
       ElseIf cell.Text = "0007" Then
            cell.Text = "0001"
       ElseIf cell.Text = "0008" Then
            cell.Text = "0011"
       ElseIf cell.Text = "0009" Then
            cell.Text = "0003"
        End If
Next
End Sub

Could anyone fix it for me? It should be very simple for you guys...
thanks.

**JBeaucaire** · 09-10-2009, 06:48 AM

Try this:

Sub Change()
Dim Beta As Range, cell As Range

Set Beta = Range("A1:A7")
    
    For Each cell In Beta
        Select Case cell.Value
            Case 3:    cell = "'0006"
            Case 4:    cell = "'0043"
            Case 5:    cell = "'0002"
            Case 7:    cell = "'0001"
            Case 8:    cell = "'0011"
            Case 9:    cell = "'0003"
        End Select
    Next cell
    
End Sub

**Kb24** · 09-10-2009, 07:24 AM

Thanks!
Just one more thing :

How do we state that if the condition is not met,
the specific cell remain unchanged?

.
.
.
Case else : cell remains unchanged

e.g if there is 67 in column A1, then the cell remains as 67 because 67 is not in the case-select conditions

Column A1

3
4
5
7
8
9
67

Sub Change()
Dim Beta As Range, cell As Range

Set Beta = Range("A1:A7")
    
    For Each cell In Beta
        Select Case cell.Value
            Case 3:    cell = "'0006"
            Case 4:    cell = "'0043"
            Case 5:    cell = "'0002"
            Case 7:    cell = "'0001"
            Case 8:    cell = "'0011"
            Case 9:    cell = "'0003"
        End Select
    Next cell
    
End Sub

result

**Drew Talbot** · 09-10-2009, 09:26 AM

The case else stament is not needed if you do not wish the statement to do something in the event of a match not being found,

However if you wish to tack it on just to make the select statement look complete just leaving it as;

Case 3: cell = "'0006"
Case else:

**Drew Talbot** · 09-10-2009, 06:56 AM

Hi Kb24,

Try this, if I understand you the idea is your trying to switch around two numbers based on the value of the first...

The example will switch all number 3s in the range with the number 6, expand upon this yourself by copying the 'case' and the line below it replacing the values to find and replace.

PHP Code:


Sub ChangeValues()



Dim Beta As Range

Dim cell As Range



Set Beta = Range("A1:A7")



    For Each cell In Beta



    Select Case cell.Value



              Case 3



              cell.Value = 6



    End Select



    Next



End Sub

Drew

**royUK** · 09-10-2009, 07:00 AM

Your If statement is looking for "004", etc. I would think that the zeros don't exist, they are put there by Formatting so nothing would be found.

The Select case is much neater