Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare datas
I need to compare a group of lines. On my worksheet there are lines with the
same number or order (colum B) (numericaly classified) and another colum (D) with same datas, I need to compare if the number of the order are the same and if the data on the columm d at the same line are the same, if so, the macre should answer 'yes',if one of the datas on the columm d are different the macro should answer 'no'. Someone could help me? regards, Dani |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare datas
HI! Mike, tks for your help, but I think I wrote someting wrong (english isnt
my first language) and it make you do a macro for another thing.. fortunataly it will work for another job... but what I really want is: A B C D 3 31046 31046 Sim 3 31046 Sim 3 31046 não 3 31046 Sim 3 31046 Sim 3 31056 31056 Não 3 31056 Não 3 31056 Não I need to check if the datas on the column D are equals, according to the number on the column B. Eg.: for the number 31046 (5 lines) the datas on the columns D are differents (there are 4 "Sim" and 1 "no"). If all the datas on this example were "Sim" or "Não" the macro should answer on the column E = yes and if not (like in this case), should answer "no". best regards, "Mike" escreveu: Sub changeValues() Const COLUMNB As String = "B" Const COLUMND As String = "D" Const COLUMNRESULTS = "E" Dim iStartingRow As Long Dim iLastRow As Long iLastRow = Range(COLUMNB & Rows.Count).End(xlUp).Row For iStartingRow = 2 To iLastRow If Range(COLUMNB & iStartingRow).Value = Range(COLUMND & iStartingRow).Value Then Range(COLUMNRESULTS & iStartingRow).Value = "YES" Else Range(COLUMNRESULTS & iStartingRow).Value = "NO" End If Next End Sub "Dani Lima" wrote: I need to compare a group of lines. On my worksheet there are lines with the same number or order (colum B) (numericaly classified) and another colum (D) with same datas, I need to compare if the number of the order are the same and if the data on the columm d at the same line are the same, if so, the macre should answer 'yes',if one of the datas on the columm d are different the macro should answer 'no'. Someone could help me? regards, Dani |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare datas
try this:
Const COLUMNC As String = "C" Const COLUMND As String = "D" Const COLUMNRESULTS = "E" Dim iStartingRow As Long Dim iLastRow As Long Dim blnResult As Boolean iLastRow = Range(COLUMND & Rows.Count).End(xlUp).Row blnResult = True indxNewRow = 2 For iStartingRow = 2 To iLastRow If Range(COLUMNC & iStartingRow).Value = "" Then If Range(COLUMND & iStartingRow).Value < Range(COLUMND & _ iStartingRow - 1).Value Then blnResult = False End If End If If Range(COLUMNC & iStartingRow).Value < "" Or iStartingRow = iLastRow Then For indxRow = indxNewRow To iStartingRow If blnResult = False Then Range(COLUMNRESULTS & indxRow).Value = "NO" Else Range(COLUMNRESULTS & indxRow).Value = "YES" End If Next iNewValue = Range(COLUMNC & iStartingRow).Value indxNewRow = iStartingRow blnResult = True End If Next On Apr 14, 4:10*am, Dani Lima wrote: HI! Mike, tks for your help, but I think I wrote someting wrong (english isnt my first language) and it make you do a macro for another thing.. fortunataly it will work for another job... but what *I really want is: A * * * * B * * * * * * *C * * * * D 3 * * * 31046 * 31046 * Sim 3 * * * 31046 * * * * * * * * *Sim 3 * * * 31046 * * * * * * * * *não 3 * * * 31046 * * * * * * * * *Sim 3 * * * 31046 * * * * * * * * *Sim 3 * * * 31056 * 31056 * Não 3 * * * 31056 * * * * * * * * *Não 3 * * * 31056 * * * * * * * * *Não I need to check if the datas on the column D are equals, according to the number on the column B. Eg.: for the number 31046 (5 lines) the datas on the columns D are differents (there are 4 "Sim" and 1 "no"). If all the datas on this example were "Sim" or "Não" the macro should answer on the column E = yes and if not (like in this case), should answer "no". best *regards, "Mike" escreveu: Sub changeValues() Const COLUMNB As String = "B" Const COLUMND As String = "D" Const COLUMNRESULTS = "E" Dim iStartingRow As Long Dim iLastRow As Long iLastRow = Range(COLUMNB & Rows.Count).End(xlUp).Row For iStartingRow = 2 To iLastRow * * If Range(COLUMNB & iStartingRow).Value = Range(COLUMND & iStartingRow).Value Then * * * * Range(COLUMNRESULTS & iStartingRow).Value = "YES" * * Else * * * * Range(COLUMNRESULTS & iStartingRow).Value = "NO" * * End If Next End Sub "Dani Lima" wrote: I need to compare a group of lines. On my worksheet there are lines with the same number or order (colum B) (numericaly classified) and another colum (D) with same datas, I need to compare if the number of the order are the same and if the data on the columm d at the same line are the same, if so, the macre should answer 'yes',if one of the datas on the columm d *are different the macro should answer 'no'. Someone could help me? regards, Dani- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to compare datas
Thanks, worked perfect!
"dk" escreveu: try this: Const COLUMNC As String = "C" Const COLUMND As String = "D" Const COLUMNRESULTS = "E" Dim iStartingRow As Long Dim iLastRow As Long Dim blnResult As Boolean iLastRow = Range(COLUMND & Rows.Count).End(xlUp).Row blnResult = True indxNewRow = 2 For iStartingRow = 2 To iLastRow If Range(COLUMNC & iStartingRow).Value = "" Then If Range(COLUMND & iStartingRow).Value < Range(COLUMND & _ iStartingRow - 1).Value Then blnResult = False End If End If If Range(COLUMNC & iStartingRow).Value < "" Or iStartingRow = iLastRow Then For indxRow = indxNewRow To iStartingRow If blnResult = False Then Range(COLUMNRESULTS & indxRow).Value = "NO" Else Range(COLUMNRESULTS & indxRow).Value = "YES" End If Next iNewValue = Range(COLUMNC & iStartingRow).Value indxNewRow = iStartingRow blnResult = True End If Next On Apr 14, 4:10 am, Dani Lima wrote: HI! Mike, tks for your help, but I think I wrote someting wrong (english isnt my first language) and it make you do a macro for another thing.. fortunataly it will work for another job... but what I really want is: A B C D 3 31046 31046 Sim 3 31046 Sim 3 31046 não 3 31046 Sim 3 31046 Sim 3 31056 31056 Não 3 31056 Não 3 31056 Não I need to check if the datas on the column D are equals, according to the number on the column B. Eg.: for the number 31046 (5 lines) the datas on the columns D are differents (there are 4 "Sim" and 1 "no"). If all the datas on this example were "Sim" or "Não" the macro should answer on the column E = yes and if not (like in this case), should answer "no". best regards, "Mike" escreveu: Sub changeValues() Const COLUMNB As String = "B" Const COLUMND As String = "D" Const COLUMNRESULTS = "E" Dim iStartingRow As Long Dim iLastRow As Long iLastRow = Range(COLUMNB & Rows.Count).End(xlUp).Row For iStartingRow = 2 To iLastRow If Range(COLUMNB & iStartingRow).Value = Range(COLUMND & iStartingRow).Value Then Range(COLUMNRESULTS & iStartingRow).Value = "YES" Else Range(COLUMNRESULTS & iStartingRow).Value = "NO" End If Next End Sub "Dani Lima" wrote: I need to compare a group of lines. On my worksheet there are lines with the same number or order (colum B) (numericaly classified) and another colum (D) with same datas, I need to compare if the number of the order are the same and if the data on the columm d at the same line are the same, if so, the macre should answer 'yes',if one of the datas on the columm d are different the macro should answer 'no'. Someone could help me? regards, Dani- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
transfer datas into different sheet using macro? | Excel Discussion (Misc queries) | |||
Macro to compare datas | Excel Programming | |||
Trying to compare the datas... | Excel Worksheet Functions | |||
re : Finding the datas and deleting datas which are not found. | Excel Programming | |||
Create a Worksheet by getting datas from the txt file with Macro | Excel Programming |