Match columns to rows then loop.
 

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

Match columns to rows then loop.
 

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
---------------
Jacob Skaria


"stumpy" wrote:

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

Match columns to rows then loop.
 

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

--
Rick (MVP - Excel)


"stumpy" wrote in message
...
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