VBA Code if, then statement?

[stvnhdmpa]

I am new to VBA and I'm struggling to learn it. What I am trying to do is an inventory management type of thing. I have two worksheets, (Sheet1) with a list of items in (Column E) and (Sheet2) where I enter items as they are used in (column A). I am trying to get my code to look in (Sheet2) (Column A) and if the values match values in Sheet2 Column E, it will delete it. I would like it to start at the top and work the way down to the end of the items in Sheet 2 and only delete one at a time.

I'm not sure if the "IF/Then" statement is the best or not. Any help you can provide would be great.

Thanks.

 

[Glory]

Why did you mention sheet1 at all? Does the macro need to delete stuff from sheet1 or sheet2?

Edit: Do you want the code to look in Column 1 of "Sheet1" and compare each cell to Column 5 of "Sheet2", and delete any duplicates from Sheet2?

What about the leftover blank rows, how do you want to handle that?

 

[Glory]

Sub test()
 
Dim theRange1, theRange2 As Range
 
Set theRange1 = Intersect(ThisWorkbook.Sheets("Sheet1").Range("E:E"), _
                ThisWorkbook.Sheets("Sheet1").UsedRange)
 
Set theRange2 = Intersect(ThisWorkbook.Sheets("Sheet2").Range("A:A"), _
                ThisWorkbook.Sheets("Sheet2").UsedRange)
 
If theRange1 Is Nothing Then: MsgBox "No data in column E on Sheet1!": Exit Sub
If theRange2 Is Nothing Then: MsgBox "No data in column A on Sheet2!": Exit Sub
 
For Each cell1 In theRange1
 
    For Each cell2 In theRange2
 
        If cell2 = cell1 Then cell2.Value = ""
 
    Next
 
Next
 
End Sub

 

[stvnhdmpa]

Thanks Glory,

If I were to do it manually, I would:
1) go to Shee2 "A2" Copy
2) go to sheet1, highlight column E
3) Do a find with "control F"
4) Paste "control P" with the value from "Sheet 2.A2"
5) Click the find next button
6) Select Cell that it finds and matches
7) Delete
8) Go back to Sheet2 and delete the contents of A2
9) Go to the next cell in the column (if not blank)
10) Loop this cycle until Sheet2 Column A is blank.
11)End

 

[Glory]

So does this code do what you want it to do?

 

[Peter_SSs]

If I were to do it manually, I would:
1) go to Shee2 "A2" Copy
2) go to sheet1, highlight column E
3) Do a find with "control F"
4) Paste "control P" with the value from "Sheet 2.A2"
5) Click the find next button
6) Select Cell that it finds and matches
7) Delete
8) Go back to Sheet2 and delete the contents of A2
9) Go to the next cell in the column (if not blank)
10) Loop this cycle until Sheet2 Column A is blank.
11)End

Welcome to the MrExcel board!

Test this in a copy of your workbook.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ReplaceValues()<br>    <SPAN style="color:#00007F">Dim</SPAN> rSh2 <SPAN style="color:#00007F">As</SPAN> Range, cSh2 <SPAN style="color:#00007F">As</SPAN> Range, Found <SPAN style="color:#00007F">As</SPAN> Range<br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet2")<br>        <SPAN style="color:#00007F">Set</SPAN> rSh2 = .Range("A2", .Range("A" & .Rows.Count).End(xlUp))<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("Sheet1").Columns("E")<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cSh2 <SPAN style="color:#00007F">In</SPAN> rSh2<br>            <SPAN style="color:#00007F">If</SPAN> cSh2.Value <> "" <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#00007F">Set</SPAN> Found = .Find(What:=cSh2.Value, LookAt:=xlWhole, _<br>                    MatchCase:=False, SearchFormat:=False)<br>                <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">Not</SPAN> Found <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>                    Found.ClearContents<br>                    cSh2.ClearContents<br>                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> cSh2<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>End <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[stvnhdmpa]

Thank you both for your help. I was able to get the code working.