Comparing values from sheet1 with sheet2 values

Question

I have this code

For i = 1 to 9999
    If sheets ("sheet1").cells (i, 5).value >= sheets ("sheet2").cells (i, 8).value And sheets ("sheet1").cells (i, 5).value <= sheets ("sheet2").cells (i, 11).value then

    Sheets ("sheet1").cells (i, 10).value = "true"

What the code is suppose to do is check if value on sheet1 is within a range of values between two figures on sheet2. But it doesn't seem to work. Any help would be much appreciated.

Other than the misplaced spaces (Sheets ("Sheet1") should be Sheets("Sheet1")) and the fact that it's commented out... it looks fine to me. What does "Doesn't seem to work" mean? It says (for clarification) if the contents of column E is between column H and K then print "true" in column J. Is that what you want it to do? — JNevill
– JNevill, Commented Oct 2, 2018 at 21:14
...I assume when you're actually running the code, the lines aren't commented out? — BruceWayne
– BruceWayne, Commented Oct 2, 2018 at 21:15
Yes @ JNevill. @ brucewayne no comments when actually running the code. — Ikem Michael
– Ikem Michael, Commented Oct 2, 2018 at 21:22
I'm not sure we can offer more help here without more information. Is it erroring, is "True" not showing up. Can you share some sample data and your desired results? — JNevill
– JNevill, Commented Oct 2, 2018 at 21:25

Ikem Michael · Accepted Answer · 2018-10-10 09:12:13Z

1

Sub workbook_initialize()

Dim cell As Excel.Range

Dim LastRow As Long



LastRow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row



For Each cell In Sheets("sheet1").Range("E1:E" & LastRow)
For i = 1 To Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row


If cell.Value >= Sheets("Sheet2").Cells(i, 8).Value And cell.Value <= Sheets("Sheet2").Cells(i, 11).Value Then


Sheets("Sheet1").Cells(cell.Row, 10).Value = Sheets("Sheet2").Cells(i, 3).Value




End If
Next i
Next


End Sub

I was able to achieve what i wanted with this code. which is to loop through a particular range of cells in sheet1 and if any cell has value greater than or equal to value in sheet2 column H and at the same time less than or equal to the value of sheet2 column K on same row then make sheet1 column J same value as the corresponding cell in sheet2 column C.

answered Oct 10, 2018 at 9:12

Ikem Michael

632 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ikem Michael Over a year ago

despite that the code worked i discovered a new problem when i tried to add an else statement to rather write a string on any cell in sheet1 column J that doesnt meet the condition. i'lld be much grateful if i could get a solution to this new problem.

Collectives™ on Stack Overflow

Comparing values from sheet1 with sheet2 values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related