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.

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

Open in new window

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

Roy Cox

Attach an example workbook

Sunil Kakkar

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

regmigrant

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

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

Open in new window

Sunil Kakkar

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

regmigrant

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

Sunil Kakkar

ASKER

Dear regmigrant ,

This line of code is giving syntax error Compiler error

  if cell2.row = cell.row and Foundone = False

Open in new window

Please suggest remedial measures.

Thanking You,

Sunil Kakkar

ASKER CERTIFIED SOLUTION

regmigrant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sunil Kakkar

ASKER

Hi regmigrant,

Thanks a lot. Your proposed solution works like a charm.

Sunil Kakkar

Sunil Kakkar

ASKER

Very helpful and responsibe. Good Analytical approach.

regmigrant

Thanks Sunil, hope it progresses well