Loop,Compare,Write

[Michael168]

Can some gurus help the below VBA module?

1. How to go to col C:E of the last used row of the active sheet?

2. How to loop through col C:E then loop again the same range but
skipping one row upwards until I find out 8 different values from the
range C:E and these 8 different values will be written in columns G:N
in the last row of the sheet.

3. The process will repeated the above procedure again from 2nd last
row until the range C:E of the 1st row of sheet have encounter.The 2nd
time loops the new 8 values will be written in the 2nd last row of
cells G:N. The 3rd time loops the new 8 values
will be written in the 3rd last row and so on.

Any help is appreciated.

Regards
Michael

 

[Bernie Deitrick]

Michael,

See my code below.

HTH,
Bernie
MS Excel MVP

Sub TryNow()
Dim myRange As Range
Dim myRow As Long
Dim i As Integer
Dim j As Integer
Dim myCnt As Integer

With ActiveSheet.UsedRange
myRow = .Rows(.Rows.Count).Row
End With


While myRow > 0
myCnt = 0
Set myRange = Range("C" & myRow & ":E" & myRow)
For i = 1 To -myRow + 2 Step -1
For j = 1 To 3
If Application.CountIf(Range("G" & myRow & ":N" & myRow), _
myRange.Cells(i, j).Value) = 0 Then
Range("IV" & myRow).End(xlToLeft)(1, 2).Value = _
myRange.Cells(i, j).Value
myCnt = myCnt + 1
If myCnt = 8 Then GoTo Found8:
End If
Next j
Next i
Found8:
myRow = myRow - 1
Wend


End Sub

Can some gurus help the below VBA module?

1. How to go to col C:E of the last used row of the active sheet?