Loop to compare Cells in a Column

[KORKIS2]

So I want to go down a column and compare cells.
I know alot of C programming but not much VBA.

So here is a flow of what I want.
Lets Say

Basically keep looping and working down the Column until the next spot it larger than previous then exit the loop.



P= blank array
X= Cell Value A1
Y= Cell A2

IF X <Y Then
Y stored in P exit
Else Y+1 =Y(Meaning A3)
Return to IF

Basically keep looping and working down the Column until the next spot it larger than previous then exit the loop.

 

[ChrisM]

You've changed the start row to 8, you told me earlier that it started on 7. You can't change that number without changing other parts of the code. Please set it back to 7 temporarily (in both your worksheet and in the VBA code) for testing purposes.

Is Sheet1 the correct sheet name for the sheet you are working with? Do you see the list of sheet names in the window pane on the left hand side of your screen? Sheet1 is the sheet codename, not the name you see in the workbook itself. The displayed sheet names are in parenthesis in that window. Make sure you are using the correct code name, or if you want to use the true sheet name, replace all instances of "sheet1" with 'Worksheets("yoursheetname") '

Finally, let's make sure you are actually running the code. add one additional line at the very end:

msgbox "test"

then run the code and make sure that message box comes up.

 

[KORKIS2]

The messege box pops up I changed everything back to 7 I actually just recopied the code you put in originally. I made sure the sheet name was sheet1
I don't understand. I've been looking at it all afternoon.

 

[ChrisM]

Try this code with the additional messageboxes:

Sub GetRows()

Dim lLastRow As Long
Dim aSrc As Variant
Dim aResults() As String   'item, start row, stop row
Dim i As Long, k As Long
Dim sCurrVal As String

lLastRow = Sheet1.Cells(Sheet1.Rows.Count, "A").End(xlUp).Row
msgbox llastrow
aSrc = Sheet1.Range("A7:A" & lLastRow) 'this is your raw data

sCurrVal = aSrc(1, 1) 'set current item
ReDim aResults(1 To 3, 1 To 1) 'create result array
aResults(1, 1) = sCurrVal
aResults(2, 1) = 7 'assumes your data starts in row 1

k = 1 'result counter
 
For i = 1 To UBound(aSrc, 1)
    If aSrc(i, 1) <> sCurrVal Then
        'write stop row value
        aResults(3, k) = i + 5
        'set new search item
        sCurrVal = aSrc(i, 1)
        'load result array
        k = k + 1
        ReDim Preserve aResults(1 To 3, 1 To k)
        aResults(1, k) = sCurrVal
        aResults(2, k) = i + 6
    End If
Next i
aResults(3, k) = i + 6 'this will capture the last entry
msgbox k & " items searched"
msgbox "Result array first entry: " & aresults(1,1) & " " & aresults(2,1)
'now write result array to worksheet in 3 columns
Sheet1.Range("D1").Resize(k, 3) = Application.Transpose(aResults)

End Sub

report back what each of those message boxes says

 

[KORKIS2]

137
63 items searched
Result array first entry: FCC ID: 7

 

[KORKIS2]

Its got to be searching the wrong sheet

 

[KORKIS2]

Everything I see about sheets it points to Sheet1

 

[KORKIS2]

What were you talking about with defining the sheet on the left side of the panel I only know how to change the name by going to the bottoms tabs

 

[ChrisM]

change every instances of "Sheet1" in my code to:

Worksheets("Your sheet name here")

Again, "Sheet1" is the sheet codename, which you can only see in the visual basic editor, in the top left corner. And yes, by default in a new workbook, my "Sheet1" will always point to workbook "Sheet 1"

 

[KORKIS2]

wow got it thank you very much : )!!!!!!!!
You have been great thank you very much

 

[ChrisM]

Whew! glad we got there in the end!