case select question

[Mark_G]

I was wondering if it is possible to do a case select on the value of the cell not being equal to another cell. I have written it using an if statement, but was wondering if the same could be achieved using case select.

For Each Cell In Range("A2:A" & LR).Cells
        If Cell.Value <> Cell.Offset(0, 6).Value Then
            Range(Cells(Cell.Row, "c"), Cells(Cell.Row, "ab")).Insert
            Cell.Offset(0, 6).Value = Cell.Value
            Cell.Offset(0, 8).Value = Cell.Offset(0, 1).Value
        End If
Next Cell

The code currently looks to see if the value in column a is equal to that in column g, and if it is not, then it inserts cells and puts a value in column g and i.

Mark

 

[Smitty]

You could, but as you're only testing for one condition it kind of defeats the purpose of a Select Case argument, which is generally used to evaluate multiple conditions.

In this case, Select Case is a longer statement:

<font face=Courier New>For Each Cell In Range("A2:A" & LR).Cells
        Select Case Cell.Value
            Case Is <> Cell.Offset(0, 6).Value
                Range(Cells(Cell.Row, "c"), Cells(Cell.Row, "ab")).Insert
                Cell.Offset(0, 6).Value = Cell.Value
                Cell.Offset(0, 8).Value = Cell.Offset(0, 1).Value
        <SPAN style="color:#00007F">End</SPAN> Select
<SPAN style="color:#00007F">Next</SPAN> Cell</FONT>

HTH,

Smitty

 

[Mark_G]

Thanks for that. If select case the best for this case, is there any other way to speed the code up. There is about 9000 rows of data to go through and it takes forever to get through them all. There is nothing else in my code, so is there anyway to speed things up?

Here is the full code.

Sub correction()

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

LR = ActiveSheet.Range("A65536").End(xlUp).Row

For Each Cell In Range("A2:A" & LR).Cells
        If Cell.Value <> Cell.Offset(0, 6).Value Then
            Range(Cells(Cell.Row, "c"), Cells(Cell.Row, "ab")).Insert
            Cell.Offset(0, 6).Value = Cell.Value
            Cell.Offset(0, 8).Value = Cell.Offset(0, 1).Value
        End If
Next Cell

For Each Cell In Range("B2:B" & LR).Cells
        If Cell.Value <> Cell.Offset(0, 7).Value Then
            Range(Cells(Cell.Row, "c"), Cells(Cell.Row, "ab")).Insert
            Cell.Offset(0, 7).Value = Cell.Value
            Cell.Offset(0, 5).Value = Cell.Offset(0, -1).Value
        End If
Next Cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Thanks

 

[Norie]

Mark

Are you sure you need to loop twice?

 

[Mark_G]

Thanks for the suggestions. I realized the code was going slope not due to the way it was written, but to do with my data. I was trying to match up hours in the two columns, but for some reason some of the times would not match up, even though the decimal numbers seemed to be the same. I got around this by making them whole numbers (ie 8:00 just become 8).

Once again thanks, I was able to clean the code up a bit with your suggestions.

Mark