Help with Do While Statement

RWayneH
RWayneH used Ask the Experts™
on
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.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2015

Commented:
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 ChongSoftware Team Lead

Commented:
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

Author

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 Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Ryan ChongSoftware Team Lead

Commented:
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

Top Expert 2015

Commented:
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

Author

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.

Author

Commented:
Or am I reading the solutions wrg?
Top Expert 2015

Commented:
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...

Author

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
Top Expert 2015

Commented:
Which sheet you are running this macro on??

Saurabh

Author

Commented:
Action1
Top Expert 2015

Commented:
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??

Author

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.

Author

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

Commented:
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??

Author

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 ChongSoftware Team Lead

Commented:
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.

Author

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

Author

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 ChongSoftware Team Lead

Commented:
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?

Author

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.

Author

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.
Top Expert 2015
Commented:
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...

Author

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

Author

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?

Author

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

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

Saurabh...
Top Expert 2015

Commented:
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..
Software Team Lead
Commented:
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

Author

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...

Author

Commented:
EXCELent!!  Thanks for the help.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial