Match columns to rows then loop.

[stumpy]

I am trying to match numbers in sheet1 column A to rows in sheet2 (row
3),then if there is a match find the last number in the column that
matches(sheet2),if that is above 5 then enter 1 into sheet1 column b next to
the value that has been matched

eg
sheet1!A1 = 123 (needs to be matched in sheet2)
sheet2!C3 = (matched number) 123, find last value in column c
if last value in column c is greater than 5 then
sheet1!B1 = 1
then loop through all rows in Sheet1 column A

any help is greatly appreciated

 

[Jacob Skaria]

Try the below macro

Sub Macro2()
Dim lngRow As Long, varTemp As Variant
Dim ws1 As Worksheet, ws2 As Worksheet

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

For lngRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
If Trim(ws1.Range("A" & lngRow)) <> "" Then
If WorksheetFunction.CountIf(ws2.Rows(3), _
ws1.Range("A" & lngRow)) > 0 Then
lngCol = WorksheetFunction.Match(ws1.Range("A" & lngRow), _
ws2.Rows(3), 0)
Set varTemp = ws2.Cells(Rows.Count, lngCol).End(xlUp)
If varTemp.Row <> 3 Then
If CInt(varTemp.Value) > 5 Then ws1.Range("B" & lngRow) = 1
End If
End If
End If
Next
End Sub

If this post helps click Yes

 

[Rick Rothstein]

Here is another approach you can try...

Sub Marine()
Dim WS1 As Worksheet, WS2 As Worksheet, R As Range, C As Range
Dim StartAt As Range, X As Long, StartRow As Long, StartCol As Long
Dim LR1 As Long, LR2 As Long, GreaterThanAmount As Long
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
StartRow = 3
StartCol = 1
GreaterThanAmount = 5
On Error Resume Next
LR1 = WS1.Cells(WS1.Rows.Count, StartCol).End(xlUp).Row
For X = 2 To LR1
Set StartAt = WS1.Cells(X, StartCol)
Set R = WS2.Rows(StartRow).Find(StartAt.Value, _
LookAt:=xlWhole, MatchCase:=False)
If Not R Is Nothing Then
LR2 = R.EntireColumn.Find("*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious).Row
Set C = WS2.Cells(LR2, R.Column)
If IsNumeric(C.Value) Then
If C.Value > GreaterThanAmount Then StartAt.Value = 1
End If
End If
Next
End Sub