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

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
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, _
Set C = WS2.Cells(LR2, R.Column)
If IsNumeric(C.Value) Then
If C.Value > GreaterThanAmount Then StartAt.Value = 1
End If
End If
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question