Create a header by determining when two cells do not match and pasting header from another sheet

Question

I'm looking for something that is able to start searching in row 9 of column C for cells that do not equal cells 2 rows below. E.g. C9 <> C11, C10 <> C12, etc. Then Copy a cell from another sheet and paste it into Column A in the same row where there was the first discrepancy. E.G. if C9 <> C11, then paste from Sheet2 into A11. The purpose is to insert a new header after already formatting and sorting the data, below is one of the many variations I have tried, receiving only errors or blank inputs.

Any help would be greatly appreciated.

Dim iRow2 As Integer, iCol2 As Integer, iRow3 As Integer, iCol3 As Integer
Dim oRng2 As Range
Dim oRng3 As Range
Dim qqq As Range

Set oRng2 = Range("C9:C80")
Set oRng3 = Range("A9:A80")

iCol2 = oRng2.Column
iCol3 = oRng3.Column

For Each qqq In oRng2
Do
If qqq.Cells(oRng2, 3) <> qqq.Cells(oRng2 + 2, 3) Then
 ThisWorkbook.Worksheets("Sheet1").Range("N1").Copy Destination = Sheets("Sheet2").Range(oRng2 + 2, 1)

End If
Loop While Not Cells(iRow2, iCol2).Text = ""
Next

LWChris LWChris · Accepted Answer · 2019-04-10T23:01:18

I suggest you work with column numbers if you can. Then you will probably see that you need far less indices than your current code does, and you don't forget to update the indices (like currently iRow2 and iCol2) so easily.

Dim dataSheet As Worksheet
Dim headerSheet As Worksheet

Set dataSheet = ActiveWorkbook.Sheets(2)
Set headerSheet = ActiveWorkbook.Sheets(1)

Dim r As Integer
For r = 9 To 80
  If dataSheet.Cells(r, 3).Value <> dataSheet.Cells(r + 2, 3).Value Then
    dataSheet.Cells(r, 1).Value = headerSheet.Cells(1, 14).Value
  End If
Next r

This should give you a good start. I don't know that exactly the inner loop is meant to check, because you're never touching iRow2 and iCol2 it will try to check the cell at R0C0 which is an invalid address. If you want to make sure the row you compare with isn't empty, check that first within the For loop:

...
For r = 9 To 90
  If dataSheet.Cells(r + 2, 3).Value = "" Then
    Exit For
  ElseIf dataSheet.Cells(r, 3).Value <> dataSheet.Cells(r + 2, 3).Value Then
    dataSheet.Cells(r, 1).Value = headerSheet.Cells(1, 14).Value
  End If
Next r

Create a header by determining when two cells do not match and pasting header from another sheet

1 Answers