Help with Do While Statement

When on a given cell in a s-sheet, just not in row 1 where the headers are.  For this example cell Z10.

How would on write a Do While there is a value in row 1 of column Z???  that would work just the same if I was in cell AA10?  Goal is do while there is a value in row 1 of whatever the active cell is.
RWayneHAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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

Open in new window


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

Saurabh...
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Open in new window


try customize your loop accordingly
RWayneHAuthor Commented:
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...
Become a Microsoft Certified Solutions Expert

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Learn More!
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

Open in new window

Saurabh Singh TeotiaCommented:
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

Open in new window

RWayneHAuthor Commented:
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.
RWayneHAuthor Commented:
Or am I reading the solutions wrg?
Saurabh Singh TeotiaCommented:
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

Open 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

Open in new window


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

Saurabh...
RWayneHAuthor Commented:
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
Saurabh Singh TeotiaCommented:
Which sheet you are running this macro on??

Saurabh
RWayneHAuthor Commented:
Action1
Saurabh Singh TeotiaCommented:
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??
RWayneHAuthor Commented:
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.
RWayneHAuthor Commented:
The sample Action1 is just a reduced sheet of a few columns and rows.  Usually there are 40 columns and about 50 rows..
Saurabh Singh TeotiaCommented:
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??
RWayneHAuthor Commented:
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
RWayneHAuthor Commented:
Action1 is a sample of the data.  The other sheet tab is what I want it to look like after.
CompareTwoRowsOfData.xls
RWayneHAuthor Commented:
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.
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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?
why last 2 items were highlighted?
>>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?
RWayneHAuthor Commented:
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.
RWayneHAuthor Commented:
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.
Saurabh Singh TeotiaCommented:
Rwayne,

You can use the following code and it will do what you are looking for..

Sub highlval()
    Dim i As Long, k As Long
    i = 2
    k = 5
    Do Until i > Cells(Cells.Rows.Count, 1).End(xlUp).Row

        Do Until k > Cells(1, Cells.Columns.Count).End(xlToLeft).Column


            If Cells(i, k).Value <> Cells(i + 1, k).Value Then
                Range(Cells(i, k).Address & ":" & Cells(i + 1, k).Address).Interior.ColorIndex = 46

            End If
            k = k + 1
        Loop

        i = i + 2

        k = 5
    Loop
End Sub

Open in new window


Saurabh...
RWayneHAuthor Commented:
Thanks, so far so good.  doing some testing on a 3 row and 4 row compare.
RWayneHAuthor Commented:
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?
RWayneHAuthor Commented:
Can you comment on setting of I=2 and k=5?  Does this mean start at row 2 and begin at column D.
Saurabh Singh TeotiaCommented:
Yes..start at row-2 and 5=E so start from Column-E

Saurabh...
Saurabh Singh TeotiaCommented:
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..
Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
I did some changes and seems this works for me (if I understand you correctly)

Sub Macro1()
    Dim i As Long, k As Long
    lastRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row
    lastCol = Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    
    k = 5
    i = 2
    material = ""
    Do Until k > lastCol
        Do Until i > lastRow
            If Cells(i, 1) <> material Then
                material = Cells(i, 1)
                n = getRange(material, i)
            End If
            isSame = True
            t = Cells(i, k)
            For j = 1 To n - 1
                If Cells(i + j, k) <> t Then
                    isSame = False
                    Exit For
                End If
            Next
            If isSame = False Then
                For j = 1 To n
                    Cells(i + j - 1, k).Interior.ColorIndex = 46
                Next
            End If
            i = i + n
        Loop
        k = k + 1
        i = 2
    Loop
End Sub

Function getRange(ByVal v As String, ByVal idx As Integer) As Integer
    c = 1
    Do While Cells(idx + c, 1) = v And v <> ""
        c = c + 1
    Loop
    getRange = c
End Function

Open in new window


I have also add some dummy rows and columns for testing
CompareTwoRowsOfData-b.xlsm

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
RWayneHAuthor Commented:
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...
RWayneHAuthor Commented:
EXCELent!!  Thanks for the help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.