This should be easy...If values exists in a range then...

[mvparker79]

I would think that this should be relatively easy, but I can't seem to find the answer anywhere. Basically, if a cell value exists in a list on another worksheet then move to next row. If the cell value does NOT exist then look at columns J, K, L and M and see if a specific value exists in each. If any of these cells contain this criteria then copy the row and add it to the bottom of a growing list on another sheet. Here is what I have so far.

Sub OrdersHeld()

Application.ScreenUpdating = False

Dim x As Double
Dim ws As Worksheet
Dim numberofrows As Double

numberofrows = 0
DoEvents
Set ws = Sheets("Orders")

numberofrows = ws.UsedRange.Rows.Count

x = 1
i = 0

For i = 0 To numberofrows
If ws.Cells(x, 10).Value = "1" Or ws.Cells(x, 11).Value = "80" _
Or ws.Cells(x, 12).Value = "1" Or ws.Cells(x, 12).Value = "2" Then
ws.Cells(x, 1).EntireRow.Copy
End If

x = x + 1
Next i

End Sub

[GeneralDisarray]

couple of things.

FIRST, read here how to "wrap your code in code tags"
http://www.excelforum.com/forum-rule...rum-rules.html

SECOND, your FOR loops is confusing.
Use the type long and not double for an index, also are you meaning to loop over 'x' and not 'i'? I'm not sure why you have "i=0" and then For i = 0 ...

Also, your description above and your for loop logic don't seem to match...


Can you post an example workbook of what you are after?

[mvparker79]

About the loop, in previous code that I've written (and I'm sure it's just plain sloppy because i'm new) I have had trouble getting the code to evaluate the last row unless I set i = 0 then use For i = 0 To numberofrows. The way it is looping seems to work fine in my experience but you're more than welcome to help me pretty it up if you'd like.

I've attached a copy of a sample of workbook. The Orders worksheet is where I am doing the evaluating. Notice the 4 cells that are highlighted. Those SalesID exist on the List worksheet and should therefore be skipped. All the other rows should have columns J, K, L, and M evaluated. If any of the following is true then the row should be copied and added to the bottom of the List worksheet:

J = 1
K = 80
L = 1
M = 2

I hope this is making sense.

[mvparker79]

One more thing to add that I was just made aware of, if the SalesID exists multiple times on the Orders worksheet and the criteria is met to copy it over, then it will be the case for every time that SalesID is listed and all rows with that SalesID should be copied over. Not just the first instance.

[GeneralDisarray]

Run the macro shown below:

Please Login or Register  to view this content.

NOTE: I have a few suggestions for you.

Don't reference sheet names because they can be changed by the user - reference the sheet's "code" name (see attachment).
Don't hard-code values for columns or check values... or if you must do that make the module level constants that are easy to figure out (it's sometimes better just to use named formulas for ranges though).
UsedRange.Rows.Count can work, but sometimes can get you in trouble depending on what else is on the worksheet (end(xlDown) and the like are handy to know also)

[GeneralDisarray]

your "one more thing" is a pretty big thing :D

I'll let you puzzle out what to edit. You need to find another column to look for an "already copied over" marker. You may need to look at a combination of Sales ID and other columns (time created?) but I'll leave that up to you. The logic is the same

[mvparker79]

When I copy your code over and run it I get a compile error: variable not defined for w_orders. If I try to define it, then I get the same thing for w_list. But when I run the macro from YOUR file, it works fine.

[mvparker79]

Also, to get around that "one more thing" issue. Can't I just have it copy every instance where the criteria is met to a temporary worksheet (not immediately adding to the list worksheet)? Once it has finished looping, then copy everything from the temporary worksheet over to the list worksheet.

[GeneralDisarray]

That compile error is because you need to add the code name to your list of "excel objects" in the VB editor.

It's hard to describe, but let me post a picture for you to see what I mean.

Description.

Find the worksheet you want to name in the "Project" list of "Microsoft Excel Objects", click on a sheet (this is what you would do to see the code module).

In the Properties window, type in the code name you want next to (Name) -- this sets a read only property for that object which you can reference in code. Because it's not accessible to the user, they can't change a tab name and break your code.

You would need to name your sheets with the code names from my macro to make it run out of the box.

[GeneralDisarray]

As to your second question, just make some kind of combined column. In your sheet you have salesID, date, etc.

What you need is something that is totally unique - that's what you need to search for -- you could use the same code, but instead of "salesIdCol = 2" you would reference some other column


Try this new attachment - i added a 15th column to function as a key. NOTE if you run this on your sheet it won't work unless you add that column also to both tables.

Please Login or Register  to view this content.

[mvparker79]

Great! This has put me on the right path. I'm sure I can figure it out from here. I'll let you know if I have any other hangups.

[GeneralDisarray]

Glad to be of help

To mark your thread solved do the following:
New Method
-Go to the top of the first post
-Select Thread Tools
-Select Mark thread as Solved

Old Method
- Go to the first post
- Click edit
- Click Advance
- Just below the word "Title:" you will see a dropdown with the word No prefix.
- Change to Solve
- Click Save