VBA To copy cells in a range that contain data skipping formulas

I have the following code in a macro that selects all cells in the range. This works fine when the range on the sheet is either null or contains data. What i am looking for is a way to modify this so that it does not copy cells that contain a formula, only actual values. Any help is greatly appreciated.

Please Login or Register  to view this content.

Thank you mrice, however when I change the first IF to your suggestion it still selects the entire range. I am trying to figure out how to combine both conditions. Skip blanks and where cell contains formula.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I should have posted a copy of the sheet am working on for this as well. It is shown below. The formulas are all based on cells to the left having data, so some of the cells may contain actual values and others just the formula. With the addition of both the last two pieces of code I am getting the "No Cells Were Found" error.

CarbPic.JPG

Formula:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Column N contains the total carbs for the line, there are five lines per section on the Daily Meals sheet, not all 5 lines will be used in most cases. So, for lines that do contain a food item, col N will contain the product of Servings and Carbs\Serving. I want to allow for a submit process that will copy over the item and the total carbs to a log sheet.

For some reason this will not allow me to attach my file, even using the advanced reply method.

In this cells Range("N4:N8"). all have formulas, hence for getting the error.
You can try this one

Please Login or Register  to view this content.

Now this is triggering the Object variable or With block variable not set error. By the way, thank you for your assistance on this, it is very much appreciated.

Please Login or Register  to view this content.

If you look at my code, I have defined my variables. You need to define the find function as an object (Range)

Please Login or Register  to view this content.

I have it at the top of the sub, here is the full sub as it stands now, there will be the paste code in at the end as well, once I get past this issue.

Please Login or Register  to view this content.

You have removed the Rng function from the code.

Set rng = Nothing should be at the bottom, just before the end sub

Please Login or Register  to view this content.

I have two different copy loops in the sub, 1 for the Items and the other for the Total Carbs section, so rather than using 2 rng variables, I am pasting in the items, then setting the rng variable to nothing and then run the second loop to get the carbs. I do not do this, excel will try to copy two non-connecting ranges, it doesn't like that.

I ran this as a stand alone sub and it also produced the Object variable or With block variable not set error

Please Login or Register  to view this content.

I really do not know with out seeing the sample data, but it might be this line

Rng.Select

This could be your code does not find any value, so nothing to select hence for the error.

You could change it to

Please Login or Register  to view this content.

We then go back to square one as to why the code is not working. Please attach a sample to short the circular journey

I think I see what is happening here. when I step into the code, the

Please Login or Register  to view this content.

line returns true even though the cell contains the data that is the result of the formula therefore skipping the rest of the code

Please Login or Register  to view this content.

this would explain why I was getting the No Cells Were Found error, and possibly the block error seeing as the range would be empty based on the code being skipped.

This has turned into being a way more complex issue that what I had thought it was. I also used I.E. rather than edge on this reply, which does seem to let me attach the book, the whole file should now be on here.

Right, that is what I was trying to figure out, how to only copy the cells that have a product from the formula but not copy the ones that do not have a product (result). Does VBA have the ability to do this, so far, it looks like that answer is no.

I am not sure I understand what you are after. The reverse would be to copy cells which have formulas only.

You can change this line

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

Or change these line in to true

Please Login or Register  to view this content.

INTO

Please Login or Register  to view this content.

With the sheet the way it is now, what I want this to do is to copy N4:N6 as these are the only lines where an item (column K) exist. In each of the changes above, the entire range of N is selected (N4:N8)

what I may need to do is set the range to only go to the last row that contains data in a range of the K column.

You need to add another if statements

If cell.Offset(, -3) <> "" Then

Please Login or Register  to view this content.

VIOLA' PERFECT.

Thank you so much. This works like a charm, awesome.