Help with Do While Statement

I'm assuming you want to loop in columns using do while loop then you will do something like this..

    Dim k As Long, z As Long

    k = 1
    z = 1

    Do Until z > 10

        Cells(k, z).Value = 1

        z = z + 1

    Loop

Select allOpen in new window

This populate values in row-1 starting from A Column till the time column count reaches 10..

Saurabh...

if you want to do a loop from cell Z10, you can try something like:

Sub test()
    i = 10
    Do While Cells(i, "Z") <> ""
        Debug.Print "Z" & i & " = " & Cells(i, "Z")
        i = i + 1
    Loop
End Sub

Select allOpen in new window

try customize your loop accordingly

the cell chg's is there a way to make it dynamic?  So no matter what cell it is on, it looks back up at the header row for a value..  active cell could be g6, u22, AA100  .....  no matter what cell the active cell is, if there is a value in row 1 of whatever column it is in... continue.... until the selected cell, whatever it is has a blank header in it...

if you want to know where's your active cell, you can refer to:

ActiveCell.Row and ActiveCell.Column accordingly.

hence, to loop through that column, you can try:

Sub test()
    Row = ActiveCell.Row
    col = ActiveCell.Column
    
    Debug.Print "Active Cell at Row: " & Row & ", Col: " & GetColumnName(col)
    Debug.Print "Rows with values till active cell:"
    
    For i = 1 To Row
        If Cells(i, col) <> "" Then
            Debug.Print GetColumnName(col) & i & " = " & Cells(i, col)
        End If
    Next
End Sub

Function GetColumnName(ByVal colNum As Integer) As String
    Dim d As Integer
    Dim m As Integer
    Dim name As String
    d = colNum
    name = ""
    Do While (d > 0)
        m = (d - 1) Mod 26
        name = Chr(65 + m) + name
        d = Int((d - m) / 26)
    Loop
    GetColumnName = name
End Function

Select allOpen in new window

You can use the following code to use it on activell row to do what you are looking for..

Dim k As Long, z As Long

    k = ActiveCell.Row
    z = 1

    Do Until z > 10

        Cells(k, z).Value = 1

        z = z + 1

    Loop

Select allOpen in new window

The solution suggestions are all assuming row 10...  I could have hundreds of rows...  the row of 10 was just something that I picked.  This is dynamic..  after I perform the cell coloring on mismatched data and I move over to the next column I need to check again.  Is there a value in row 1.  So I could be on row 10 that I compare with row 11, but I will move to the next column over until all columns have been checked...  after that it moves down the sheet and compares rows 12 and 13... until no more rows of data exist.  So, this is not specific to row10 and has to be dynamic.

Or am I reading the solutions wrg?

Then if you want to move rows you can do something like this...

Dim k As Long, z As Long

k = ActiveCell.Row
z = 1

Do Until k > 25
    Do Until z > 10

        Cells(k, z).Value = 1

        z = z + 1

    Loop
    k = k + 1
Loop

Select allOpen in new window

Also any particular reason you want to this by do loop because you can do it easily with for loop by defining your range and it will automatically check all the cells in your range which is like this...

Dim rng As Range, cell As Range

Set rng = Range("A1:AB100")

For Each cell In rng


Next cell

Select allOpen in new window

Now we can work on to how to create a dyanmic range

Saurabh...

Here is a sample s-sheet, however the macro I wrote only works on the first pair.  Please run macro1 and you will get an idea of what I am trying to accomplish.  I need this to go thru the whole sheet.  Datasets will not be that big so I figured the loop approach would work.  I am open to other suggestions there too.
CompareTwoRowsOfData.xls

Which sheet you are running this macro on??

Saurabh

Action1

I'm confused  here what you are trying to do here?? In plain English you are trying to compare Column-E and F with each other?? or Column-F and G with each other??

no we are comparing row data, row 2 and 3, is what Macro1 does, but it is the only pair of rows that are compared...  but I then would like it to move to row 4 and 5,  6 and 7...  until there is row more rows, for as many columns that are there.  Row and columns will be different each time.. so it needs to be dynamic...  Hope this makes sense.

The sample Action1 is just a reduced sheet of a few columns and rows.  Usually there are 40 columns and about 50 rows..

Okay so if i understand correctly this is what you want to do..You want to compare line-2 with line-3 and then line-3 with line-4 as in all the columns and so on??

Once you compare post that what the output you are looking for..i.e. when values match and when they dont match??

Close... next compare is for Ln4 and Ln5... the data is in pairs, so once a compare is done it vertically should goto the next pair.  The output would be any row that has a highlighted as a mismatch.  I was looking for a light red for mismatches, if they match, leave them as is.

can you upload a sample which is the output of what you trying to achieve? sorry as after reading your comments I'm still not quite sure your requirements.

Action1 is a sample of the data.  The other sheet tab is what I want it to look like after.
CompareTwoRowsOfData.xls

One possible issue could be if the data comes in pairs of 3 instead of pair of two like the current sample is.  User could add an Environment (column C).  If we could allow for determining how many rows are in each set???  That would be awesome...  a set of rows is determined by how many Environments there is (column C) for a given Matl (Column A).  Once a set of rows is determined it will be consistent down the sheet, to the bottom row.

ok, so can you tell us how's your latest comparison logic?

based on my understanding:
1. you trying to compare values in col E "UM" vertically first, if values of a pair is not the same, highlight them.
2. then do the same checking as in #1 for col F "MaterialGroup" and col G "GrowssWeight"

if 2 notes above are correct, then why the last 2 items in col E "UM" were highlighted as well?

>>a set of rows is determined by how many Environments there is (column C) for a given Matl (Column A)
Can you elaborate this further as well?

Sorry did not see the post....  That highlighted pair was done in error, sorry.  True that one should not have been highlighted, I was doing that manually.

On the set of rows and while looking at the data, column A is unique to a SAP client or "environment".  The user can input the environments that they want to compare.  In the example data, two were selected.  ZEC and QEC, there will be a minimum of 2 but could have any number 3  4  5...  that dictates the size of the rows being compared, but once that is determined it will be consistent for the whole sheet.  For each new matl, column A, look to column C to see how many rows are in the compare.  Hope this makes sense.  Primarily it will be 2, but not ruling out the options that it can be more.  Let me know if you need further clarification.  Thanks.

Oh and yes your understandings were correct, however remember all columns (ColD and on) and rows (Row2 and on) are dynamic and can be different each time.

Thanks, so far so good.  doing some testing on a 3 row and 4 row compare.

testing worked fine when compare set is 2 rows, however if the next extract adds a Environment, the compare set changes from 2 rows to 3  it fails.  Is there a way to determine the value of the compare sets as a variable?  and use that as a way to determine that?

At the very beginning of macro, column A will have Matl number... and the variable (rows to compare) would be  determined by the number of unique Environments for that set.  It will not ever be 1, nothing to compare, but it could be 2... 3.... 4?  90% of them will be 2.  Is it possible to determine this prior to running the compare?

Can you comment on setting of I=2 and k=5?  Does this mean start at row 2 and begin at column D.

Yes..start at row-2 and 5=E so start from Column-E

Saurabh...

And in additional right now i'm only comparing row-2 with row-3 assuming your data is sorted and their is 2 entries of each time as that's how the macro compares values..

BINGO!!!  It is working for me too.  I added a 4th instance and that worked too.  thanks for hangin in there with me on this...

EXCELent!!  Thanks for the help.