Compare Cells for VBA

[katk]

Hi all,

I'm trying to write a code that checks to see whether a cell is the same as the one above it, and if not, performs several actions. I've been trying to learn VBA from an Excel guide but it only has one relating chapter and after about 15 google searches, I decided to look for help here again.

Here's what I have right now:

Sub FormatRegions()
For Each cell In Worksheets("Rough Draft").Range("C:C")
If cell.Value <> Offset(-1, 0).Value Then
cell.Font.Bold = True
cell.FontSize 0.16 = True
EntireRow.Insert
EntireRow.Insert
End If
Next cell
End Sub

It gives me an error at the "If cell.Value" line -- that's the part I can't figure out. How do I get it to compare the value of each cell to the one above it?

Thanks so much.

 

[stnkynts]

Your close. Try:

Sub FormatRegions()
[COLOR=red]Dim cell as Range[/COLOR]
For Each cell In Worksheets("Rough Draft").Range("C:C")
If cell.Value <> [COLOR=red]cell[/COLOR].Offset(-1, 0).Value Then
cell.Font.Bold = True
[COLOR=red]cell.Font.Size = 16[/COLOR]
[COLOR=red]cell[/COLOR].EntireRow.Insert
[COLOR=red]cell[/COLOR].EntireRow.Insert
End If
Next cell
End Sub

Note: using "cell" as a variable may not be the best idea as "cells" is a predefined object. I usually use icell or mycell. Up to you.
Also you may run into problems with the loop since you are inserting rows.

 

[njimack]

Testing every cell in column C will take you a while. I'd go with something like this:

Sub FormatRegions()
Dim Last_Row As Long
Dim i As Long
Application.ScreenUpdating = False
With Sheets("Rough Draft")
    Last_Row = Range("C" & Rows.Count).End(xlUp).Row
    
    For i = Last_Row To 2 Step -1
    
        If .Cells(i, 3) <> Cells(i - 1, 3) Then
            .Cells(i, 3).Font.Bold = True
            .Cells(i, 3).FontSize 0.16 = True
            Range(.Cells(i - 1, 3), .Cells(i, 3)).EntireRow.Insert
        End If
    Next i
End With
Application.ScreenUpdating = True

End Sub

 

[VoG]

Try

Sub FormatRegions()
Dim LR As Long, i As Long
With Worksheets("Rough Draft")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 2 To LR Step -1
        If .Range("C" & i).Value <> .Range("C" & i - 1).Value Then
            .Range("C" & i).Font.Bold = True
            .Range("C" & i).FontSize 0.16 = True
            .Rows(i).Resize(2).Insert
        End If
    Next i
End With
End Sub

 

[katk]

Hi, thanks -- I implemented your changes. Unfortunately, I'm still getting an error at that line, although now it's a different one: "Method 'offset' of object 'Range' failed." Any ideas?

 

[VoG]

You need to loop backwards and stop at row 2 as in the last two posts. Otherwise you will be attempting to reference C0.

 

[njimack]

Hi, thanks -- I implemented your changes. Unfortunately, I'm still getting an error at that line, although now it's a different one: "Method 'offset' of object 'Range' failed." Any ideas?

If you're testing every cell in column C, then the first cell (C1) cannot have an offset of -1, since cell C0 doesn't exist.

A better approach would be the one I or VoG suggested, working backwards from the last cell to the first. And you don't need to use offset, since you can simply subtract 1 from the row in question.

 

[katk]

Sorry, I had posted that second response before refreshing my screen and seeing your and VoG's posts. Thanks everyone!

 

[katk]

Hi Neil, I tried your code out, my only problem is that it is inserting two rows between every row, rather than only the rows where c11 and c12 (for instance) differ. I think it's either because the cells I'm looking at contain formulas, or (since I tried just assessing the cell values by adding .value to the references) because the formula results are text. Do those seem like probable issues?

(The formula is a UDF that does a VLOOKUP from another sheet; the results are country names, like "ARGENTINA")

I also tried Peter's code but nothing seemed to happen -- I'm not sure why?

 

[VoG]

Oops!

Sub FormatRegions()
Dim LR As Long, i As Long
With Worksheets("Rough Draft")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = LR To 2 Step -1
        If .Range("C" & i).Value <> Range("C" & i - 1).Value Then
            .Range("C" & i).Font.Bold = True
            .Range("C" & i).FontSize 0.16 = True
            .Rows(i).Resize(2).Insert
        End If
    Next i
End With
End Sub