There are two sheets in the workbook. First sheet has certain values in a number of rows in a column. The second sheet also has values in each row in repeating patterns in a column. Like this:
The macro is supposed to go through each row in Sheet2 per each row in the first sheet; assign a certain value to a variable when any given row in Sheet2 includes some other certain values; compare this variable's value to the row value in Sheet1 and quit once this process has been repeated for 4 rows in Sheet1:![]()
Sheet1 Sheet2 A A 1 AAAA gre1111assdf 2 BBBB gfd2222dfhh 3 CCCC jytjtu3333dgrt 4 DDDD yufg4444fbth 5 EEEE sdfs5555gfd
This is supposed to stop once the match for the 3rd row in Sheet1 is found in Sheet2 but it only happens for the 1st row in Sheet1 and then goes into infinite loop.![]()
Sub GetWpFromXMLs() Application.ScreenUpdating = False Dim sRange As Range '// "source" Range ie. Sheet2 Dim dRange As Range '// "destination" Range ie. Sheet1 dRow = 0 '// "destination" Row ie. Sheet1 dCol = 2 '// "destination" Column ie. Sheet1 sRow = 0 '// "source" Row ie. Sheet2 Do Set dRange = ActiveSheet.Range("A1").Offset(dRow, 0) Set sRange = Sheets("Sheet2").Range("A1").Offset(sRow, 0) If InStr(1, sRange, "1111") > 0 Then critName = "AAAA" '// "AAAA" is the value of A1 in Sheet1 If InStr(1, sRange, "2222") > 0 Then critName = "BBBB" '// "BBBB" is the value of A2 in Sheet1 If InStr(1, sRange, "3333") > 0 Then critName = "CCCC" '// "CCCC" is the value of A3 in Sheet1 If InStr(1, sRange, "4444") > 0 Then critName = "DDDD" '// "DDDD" is the value of A4 in Sheet1 If InStr(1, sRange, "5555") > 0 Then critName = "EEEE" '// "EEEE" is the value of A5 in Sheet1 If dRange.Value = critName Then '// if the value extracted from the current Row in Sheet2 (sRange) matches the value of the current row in Sheet1 (dRange) MsgBox critName '// show what value was extracted dRow = dRow + 1 '// since current row in Sheet1 was taken care of, move onto the next row for the next loop sRow = 0 '// start over from the first row in Sheet2 in the next loop Else sRow = sRow + 1 '// since the current row in Sheet2 didn't match the current row in Sheet1, move onto the next row in Sheet2 End If Loop Until dRow = 4 '// stop once first 3 rows in Sheet1 are taken care of Application.ScreenUpdating = True End Sub
I am very confused. Supposed to be that once a match for the first row of Sheet1 is made, dRow is incremented by 1 and thus looking for a match in for the second row of Sheet1 while sRow is reset to 0 so it starts from the first row of Sheet2 so as not to miss any rows. What is wrong here?
Bookmarks