Compare data and delete cell

**nascarjc** · 09-21-2006, 04:46 AM

Hello All,
This is my problem which is probably simple but I am still have trouble understanding VBA.
In column "A" I have truck numbers and am adding to that list continously. In column "O" I have a list of truck numbers that reflects my inventory. What I would like to do is, check column "A" against column "O" and if the number exist in "A" then just delete the number out of the cell in "O".
I hope this makes sense, if it doesn't this is probably why I can't figure it out. If you need me to provide actual numbers I can. Thanks in advance for all the help. In fact you guys have helped me in the past and are great. Thanks again!

**jetted** · 09-21-2006, 08:34 AM

Maybe this will help you out

Sub find_sheet()
rowcount = Cells(Cells.Rows.Count, "o").End(xlUp).Row
For i = 2 To rowcount 'start second row because there is a heater
Range("o" & i).Select
val_to_check = ActiveCell.Value
'find value
Range("a1").Select
Cells.Find(What:=val_to_check, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
verif = ActiveCell.Value
'verify if value was found
'Column A header is named Trunk
If verif = "Trunk" Then
MsgBox val_to_check & " Trunk number is not present in colum A but is in column O"
GoTo line1:
End If
Range("o1").Select
'find value in column o and delete
Cells.Find(What:=val_to_check, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
verif1 = ActiveCell.Value
'Column O header is named Trunk
If verif1 = "Trunk" Then
GoTo line1:
Else
ActiveCell.ClearContents
End If
line1:
Next
End Sub

**nascarjc** · 09-21-2006, 02:26 PM

Jetted,
Thanks for the help you have given me, I pasted it in my excel sheet and ran it. What it does is delete all of column "O" whether or not the truck number is in column "A". Any suggestions. Thanks

**Myles** · 09-21-2006, 02:41 PM

try and effect these changes:

Cells.Find(What:=val_to_check, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate

SearchOrder:=xlByColumns -------->SearchOrder:=xlByRows
If your cells DO NOT contain formulas:
LookIn:=xlFormulas----------->LookIn:=xlValues

**jetted** · 09-21-2006, 03:04 PM

Hi NasCarDJ

I have amended the code in my previous email. Try it out and let me know.

THanks
Denis

**stevebriz** · 09-21-2006, 03:12 PM

try this

Please Login or Register  to view this content.

**nascarjc** · 09-23-2006, 01:31 PM

Hello All,
Thx for all the help but none of the codes work so far. Jetted, I tried the new code but still just deletes all the cells. The other codes didn't seem to do anything. Am I doing something wrong. Would it be better if I put the numbers on? Thx again all I really do appreciate it.
James

**stevebriz** · 09-24-2006, 04:30 PM

Can you post you xls or email it to me to look and see if we can find the problem?

**nascarjc** · 09-28-2006, 04:38 AM

Steve,
Thanks for your interest in helping me. Been a bad weekend and just now am able to look at answers. I will try to put this on for you by the weekend. Whenever you get to it. Thanks for the help. James

**nascarjc** · 09-29-2006, 01:22 PM

Steve,
I tried to upload my xls file but it said file type error. I don't have a zip program. Any suggestions on how I can upload this file for you? Thanks for your help
James

**jetted** · 10-05-2006, 10:30 AM

If your document is less then 100.0 KB change the extension from xls to txt. Then when we will download your file we will revert to original extension.

or
Upload your file to this location http://www.mega-file.net/, just change the extension to jpg.

Hope this help.
Denis

**nascarjc** · 10-06-2006, 04:22 AM

Jetted,
Thank you for the help I will do that with the next post

**nascarjc** · 10-06-2006, 04:27 AM

Steve and Jetted,
Attached hopefully is the file. Column A has the truck numbers that I am keeping, Column O has my inventory. I need the number deleted from O if it is in A. Thanks

**jetted** · 10-10-2006, 08:46 AM

Hi

Try this code

Sub find_sheet()
'add column to get value of column A
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("A:A").Select
Selection.Copy
Range("B1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("B1").Select

rowcount = Cells(Cells.Rows.Count, "p").End(xlUp).Row
For i = 2 To rowcount 'start second row because there is a heater
Range("p" & i).Select
val_to_check = ActiveCell.Value
'find value
Range("b1").Select
Cells.Find(What:=val_to_check, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
verif = ActiveCell.Address(rowabsolute, columnabsolute)
'verify if value was found
'Column A header is named Trunk
If verif = "P" & i Then
'MsgBox val_to_check & " Trunk number is not present in colum A but is in column O"
GoTo line1:
End If
Range("p1").Select
'find value in column o and delete
Cells.Find(What:=val_to_check, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
False).Activate
verif1 = ActiveCell.Value
'Column O header is named Trunk
If verif1 = "Trunk" Then
GoTo line1:
Else
ActiveCell.ClearContents
End If
line1:
Next
'clean up
Columns("B:B").Select
Selection.Delete Shift:=xlToLeft
Range("A2").Select
End Sub

**nascarjc** · 10-10-2006, 08:50 PM

Hello Jetted,
Thank you oh so much. That is great and works good. I don't mind the message box but if there was a way to delete that and just have it check all the way thru that would be better but it works for me and evenually I would not have that much in inventory anyway. Again thank you so much.
James

**jetted** · 10-11-2006, 07:22 AM

Hi James

I have just amend my previous post to deactivate the message box.
I am glad to see everything worked out.

Denis

**nascarjc** · 10-22-2006, 05:06 AM

Jetted,
Sorry it's been a couple weeks, been very busy, thanks for the amendment it works great just what I wanted to do. I have happy people now. Thx again.
James

Compare data and delete cell

LinkBack

Thread Tools

Rate This Thread

Display

Compare data and delete cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions