Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Could someone HELP TRANSLATE this simple psuedo code into MACRO pl
HTML Code:
for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } [size="3"](see below for explaination) I have a Excel workbook called FIRSTAM8.xls Inside I have two seperate worksheets: 1)'DATA' & 2)'WORK' There are 31886 rows in sheet 'WORK' & 5741 rows in sheet 'DATA' Column 'C' in 'WORK' contains the customer name, Column 'B' contains the zip code. In the 'DATA' sheet Column 'H' contains the customer name, & Column 'S' the zip code. I want to compare (individually, one-by-one) the zip && name Columns ('B' & 'C') of each row in sheet 'WORK' to/with/against the respective zip && name Columns ('S' & 'H') of EACH AND EVERY row in sheet 'DATA'. For example: if BOTH the zip &&and&& name columns of row1 of sheet 'WORK' matches with ANY (any of the 5741) rows of sheet 'DATA' then row1 IS a match/hit, and the entire row1 of sheet 'WORK' is highlighted yellow to indicate the match status. If row1 of 'WORK' matches to multiple rows of 'DATA' that is okay, it is still highlighted. However if row1 of 'WORK' does not match (by the criteria of zip && name) to ANY of the 5741 rows in "DATA" then it is not highlighted and the next row (row2) is processed. Note: When comparing names, I really mean comparing the first character of the customer name fields. I do this to avoid false negatives and so not to miss a potential match. This will entail using the substring manipulation functions.(?LEFT) -------------------- HTML Code:
for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } *OR* HTML Code:
for each row in WORK { for each row in DATA { if ((WORK.row.column(B) == DATA.row.column(S)) && (WORK.row.firstcharof(column(C) == DATA.row.firstcharof(column(H))) Then Highlight.interior.current.WORK.row = YELLOW, break out of current for-loop ELSE next DATA row++ } next WORK row++ } CAN SOMEONE TRANSLATE THE ABOVE INTO REAL EXCEL CODE FOR ME?? Here is a link to the ENTIRE post I made (for clarity and reference): http://www.ozgrid.com/forum/showthread.php?t=49941 Here is an abridged IMAGE of the my Excel Workbook: Attachment 4703 Here is the original file I am working on (LARGE): FIRSTAM8.xls http://www4.sendthisfile.com/d.jsp?t...Oeb2puKBql1LmB Thanks, Bo |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Could someone HELP TRANSLATE this simple psuedo code into MACRO pl
If there is no header row this should work. If there is then change the: For iRow = 1 and For iRow2 = 1 to For iRow = 2 and For iRow2 = 2 Otherwise, the following code should do the trick. Hope this helps. Sub Test() Dim iTotalWorkRows As Integer Dim iTotalDataRows As Integer Dim iCount As Integer iTotalWorkRows = Sheets("WORK").UsedRange.Rows.Count iTotalDataRows = Sheets("DATA").UsedRange.Rows.Count For iRow = 1 To iTotalWorkRows For iRow2 = 1 To iTotalDataRows If Sheets("WORK").Cells(iRow, 3).Value = Sheets("DATA").Cells(iRow2, 8).Value And Sheets("WORK").Cells(iRow, 2).Value = Sheets("DATA").Cells(iRow2, 19).Value Then Sheets("WORK").Cells(iRow, 1).EntireRow.Interior.ColorIndex = 6 iRow2 = iTotalDataRows End If Next iRow2 Next iRow End Sub -for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } [/size] ::_(see_below_for_explaination)_ I have a Excel workbook called FIRSTAM8.xls Inside I have two seperate worksheets: 1)'DATA' & 2)'WORK' There are 31886 rows in sheet 'WORK' & 5741 rows in sheet 'DATA' Column 'C' in 'WORK' contains the customer name, Column 'B' contains the zip code. In the 'DATA' sheet Column 'H' contains the customer name, & Column 'S' the zip code. I want to compare (individually, one-by-one) the zip && name Columns ('B' & 'C') of each row in sheet 'WORK' to/with/against the respective zip && name Columns ('S' & 'H') of EACH AND EVERY row in sheet 'DATA'. For example: if BOTH the zip &&and&& name columns of row1 of sheet 'WORK' matches with ANY (any of the 5741) rows of sheet 'DATA' then row1 IS a match/hit, and the entire row1 of sheet 'WORK' is highlighted yellow to indicate the match status. If row1 of 'WORK' matches to multiple rows of 'DATA' that is okay, it is still highlighted. However if row1 of 'WORK' does not match (by the criteria of zip && name) to ANY of the 5741 rows in \"DATA\" then it is not highlighted and the next row (row2) is processed. Note: When comparing names, I really mean comparing the first character of the customer name fields. I do this to avoid false negatives and so not to miss a potential match. This will entail using the substring manipulation functions.(?LEFT) -------------------- for EACH row in WORK { for each row in DATA { if ((WORK.row.zip == DATA.row.zip) && (WORK.row.name == DATA.row.name)) Then Highlight current WORK.row to YELLOW, break out of current for loop ELSE next DATA row++ } next WORK row++ } _**OR**_ for each row in WORK { for each row in DATA { if ((WORK.row.column(B) == DATA.row.column(S)) && (WORK.row.firstcharof(column(C) == DATA.row.firstcharof(column(H))) Then Highlight.interior.current.WORK.row = YELLOW, break out of current for-loop ELSE next DATA row++ } next WORK row++ } [SIZE=\"4\"]CAN SOMEONE TRANSLATE THE ABOVE INTO REAL EXCEL CODE FOR ME??:: Here is a link to the ENTIRE post I made (for clarity and reference): http://www.ozgrid.com/forum/showthread.php?t=49941 Here is an abridged IMAGE of the my Excel Workbook: 4703 Here is the original file I am working on (LARGE): FIRSTAM8.xls http://www4.sendthisfile.com/d.jsp?t...Oeb2puKBql1LmB Thanks, Bo - -- Ikaabod ------------------------------------------------------------------------ Ikaabod's Profile: http://www.excelforum.com/member.php...o&userid=33371 View this thread: http://www.excelforum.com/showthread...hreadid=536912 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Could someone HELP TRANSLATE this simple psuedo code into MACRO please?! | Excel Discussion (Misc queries) | |||
Can someone help me put psuedo code into actual excel macro?? | Excel Discussion (Misc queries) | |||
translate ws formula to vba code | Excel Programming | |||
Macro translate from number to text | Excel Programming | |||
Need code for Excel Simple Visual Basic Macro to select next avai. | Excel Programming |