case select question

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
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.

Code:
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
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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

Board Regular
Joined
Aug 6, 2004
Messages
123
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.
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
Mark

Are you sure you need to loop twice?
 

Mark_G

Board Regular
Joined
Aug 6, 2004
Messages
123
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
 

Forum statistics

Threads
1,136,878
Messages
5,678,306
Members
419,754
Latest member
LordEddard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top