Avatar of RWayneH
RWayneH
Flag for United States of America asked on

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

Avatar of undefined
Last Comment
RWayneH

8/22/2022 - Mon
Saurabh Singh Teotia

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 Chong

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
RWayneH

ASKER
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...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Ryan Chong

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 Teotia

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

RWayneH

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RWayneH

ASKER
Or am I reading the solutions wrg?
Saurabh Singh Teotia

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

ASKER
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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Saurabh Singh Teotia

Which sheet you are running this macro on??

Saurabh
RWayneH

ASKER
Action1
Saurabh Singh Teotia

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??
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RWayneH

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

ASKER
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 Teotia

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??
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
RWayneH

ASKER
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 Chong

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

ASKER
Action1 is a sample of the data.  The other sheet tab is what I want it to look like after.
CompareTwoRowsOfData.xls
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RWayneH

ASKER
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 Chong

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?
RWayneH

ASKER
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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
RWayneH

ASKER
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.
SOLUTION
Saurabh Singh Teotia

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
RWayneH

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

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RWayneH

ASKER
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 Teotia

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

Saurabh...
Saurabh Singh Teotia

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..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
Ryan Chong

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
RWayneH

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

ASKER
EXCELent!!  Thanks for the help.