How to replace cell data based on criteriaClosed

Question

Hello, 
I'm trying to write a macro in excel that will utilize two spreadsheets.  One sheet contains a large table of information while the other only contains two columns.  What I need is to find cells that match between say sheet1 column e and sheet2 column b. then based on that result either hide the row that does not have a match in sheet2 or replace say sheet1 d1 with data from sheet2 a1. then do this for the entirety of the large table.  Thanks for all the help



Configuration: Windows 7 / Firefox 3.6.18

TrowaD · Answer

Hi Craig,

See if this code yields the desired result:
Sub test()
Dim lRow, lRow2, x, y As Integer

lRow = Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Row
lRow2 = Sheets("Sheet2").Range("B" & Rows.Count).End(xlUp).Row

For Each cell In Sheets("Sheet1").Range("E1:E" & lRow)
x = 1
y = 0

Do

If cell.Value = Sheets("Sheet2").Cells(x, 2).Value Then
Sheets("Sheet2").Cells(x, 1).Copy Destination:=cell.Offset(0, -1)
y = y + 1
End If

x = x + 1

Loop Until x > lRow2

If y = 0 Then cell.EntireRow.Hidden = True

Next

End Sub
Best regards,
Trowa

How to replace cell data based on criteria

1 response

Similar discussions

Subscribe To Our Newsletter!