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.
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
I'm assuming you want to loop in columns using do while loop then you will do something like this..
This populate values in row-1 starting from A Column till the time column count reaches 10..
Saurabh...
Dim k As Long, z As Long
k = 1
z = 1
Do Until z > 10
Cells(k, z).Value = 1
z = z + 1
Loop
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:
try customize your loop accordingly
Sub test()
i = 10
Do While Cells(i, "Z") <> ""
Debug.Print "Z" & i & " = " & Cells(i, "Z")
i = i + 1
Loop
End Sub
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:
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
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
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.
Then if you want to move rows you can do something like this...
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...
Now we can work on to how to create a dyanmic range
Saurabh...
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
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
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
CompareTwoRowsOfData.xls
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??
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
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?
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.
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.
Rwayne,
You can use the following code and it will do what you are looking for..
Saurabh...
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
Saurabh...
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?
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?
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..
I did some changes and seems this works for me (if I understand you correctly)
I have also add some dummy rows and columns for testing
CompareTwoRowsOfData-b.xlsm
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
I have also add some dummy rows and columns for testing
CompareTwoRowsOfData-b.xlsm
Premium Content
You need an Expert Office subscription to comment.Start Free Trial