Sunil Kakkar
asked on
How to modify nested for loops to get the correct output.
I need to go over all rows on Sheet1 in Column X, grab its value and then, see if value is BETWEEN numbers combination stored on Sheet 2 columns A and B. If value is between, then show value from Sheet 2 Column C in the Sheet 1 Column Y.
I have written the following code to achieve my objective.
it is not printing correct values in column Y in sheet1. This is illustrated with the following image.
Help requested in modifying the code.
Thanks
Sunil Kakkar
I have written the following code to achieve my objective.
Sub FindBetweenIP()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)
For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
ip_range1 = cell2.Value2
ip_range2 = cell2.Offset(0, 1).Value2
isp = cell2.Offset(0, 2).Value2
If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
cell.Offset(0, 1).Value2 = isp
Exit For
End If
Next
Next
End Sub
it is not printing correct values in column Y in sheet1. This is illustrated with the following image.
Help requested in modifying the code.
Thanks
Sunil Kakkar
Attach an example workbook
ASKER
Hi Roy_Cox
Please find attached the example workbook. Comparing Cell values with If.xlsm
Thanks
Sunil Kakkar
comaring-cell-values-with-if-.xlsm
Please find attached the example workbook. Comparing Cell values with If.xlsm
Thanks
Sunil Kakkar
comaring-cell-values-with-if-.xlsm
looking at your sample numbers I believe you need to synchronise the loops so they are looking at the same row; at the moment you are comparing each cell in sheet1!x with every cell in sheet2!a so only the final result will appear in Y. - not the result that matches the specific row you are looking for - I am basing this on your required result for '90'
Try this
Try this
Sub FindBetweenIP()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)
For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
if cell2.row = cell.row
ip_range1 = cell2.Value2
ip_range2 = cell2.Offset(0, 1).Value2
isp = cell2.Offset(0, 2).Value2
If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
cell.Offset(0, 1).Value2 = isp
Exit For
End If
endif
Next
Next
End Sub
ASKER
Dear regmigrant ,
I could look into the problem by debug.print which you are mentioning. But my level in VBA is elementary and I am not able to find the right way to synchronise the loops. Internet search also could not help me much.
Hoping for proper modification which may serve as my guideline for future as well for similiar type of applications which regularly occur.
Thanks,
Sunil Kakkar
I could look into the problem by debug.print which you are mentioning. But my level in VBA is elementary and I am not able to find the right way to synchronise the loops. Internet search also could not help me much.
Hoping for proper modification which may serve as my guideline for future as well for similiar type of applications which regularly occur.
Thanks,
Sunil Kakkar
I added a possible solution to my answer whilst you were posting that reply :)
I've realised that the exit for might cause a problem with that loop so needs modifying:
Sub FindBetweenIP()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)
Foundone = False
For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
if cell2.row = cell.row and Foundone = False
ip_range1 = cell2.Value2
ip_range2 = cell2.Offset(0, 1).Value2
isp = cell2.Offset(0, 2).Value2
If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
cell.Offset(0, 1).Value2 = isp
Foundone = True
End If
endif
Next
Next
End Sub
I've realised that the exit for might cause a problem with that loop so needs modifying:
Sub FindBetweenIP()
Dim ws1 As Worksheet
Set ws1 = Sheets(1)
Dim ws2 As Worksheet
Set ws2 = Sheets(2)
For Each cell In ws1.Range("X2:X" & ws1.Range("X" & Rows.Count).End(xlUp).Row)
Foundone = False
For Each cell2 In ws2.Range("A2:A" & ws2.Range("A" & Rows.Count).End(xlUp).Row)
if cell2.row = cell.row and Foundone = False
ip_range1 = cell2.Value2
ip_range2 = cell2.Offset(0, 1).Value2
isp = cell2.Offset(0, 2).Value2
If (cell.Value >= ip_range1 And cell.Value <= ip_range2) Then
cell.Offset(0, 1).Value2 = isp
Foundone = True
End If
endif
Next
Next
End Sub
ASKER
Dear regmigrant ,
This line of code is giving syntax error Compiler error
Please suggest remedial measures.
Thanking You,
Sunil Kakkar
This line of code is giving syntax error Compiler error
if cell2.row = cell.row and Foundone = False
Please suggest remedial measures.
Thanking You,
Sunil Kakkar
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi regmigrant,
Thanks a lot. Your proposed solution works like a charm.
Sunil Kakkar
Thanks a lot. Your proposed solution works like a charm.
Sunil Kakkar
ASKER
Very helpful and responsibe. Good Analytical approach.
Thanks Sunil, hope it progresses well