Macro to check if range of cells values are balnk...then do something

**jayers** · 01-08-2009, 07:24 PM

Hi - I am looking for a macro that will check 2 things....then do something.
The first is if a range of cell values in are blank. lets say
Sheets("Sheet1").Range("D6:G48").
Next is if a particular cell contains a given value ,
Sheets("sheet1").Range("E5") should equal "Year 1" if true.
If both these are true then I want the macro to copy a range of cells from
Sheets("Sheet2").Range("H6:H48") to Sheets("Sheet1").Range("D6:D48").

I tried to use IsEmpty but it seems to return a "False" if a range of cells
are being tested.

Thanks for any help - Jim A

**Leith Ross** · 01-08-2009, 10:24 PM

Hello jayers,

This macro will copy the range only if both conditions are met. The CountA function will count formula that may not be visible and spaces in cells.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**stanleydgromjr** · 01-08-2009, 10:46 PM

jayers,

Try:

Please Login or Register  to view this content.

Have a great day,
Stan

**jayers** · 01-08-2009, 11:49 PM

OK...that worked great! Especially when I just had only one range of cells labeled by one cell "Year 1".

How about this though:

I have five distinct ranges of cells starting with "Year 1" through "Year 5" on Sheet1.
I have these listed in a combo box on Sheet2 (where I also have a button that activates this macro).
If "Year 3" is chosen on the combobox, how can I have the macro start checking for the "Year 3" (starting with "Year 1" which all "Years" are in row 5) and when it finds it then checks if the corresponding range is blank...Then performs the copy like you suggested.

Curious if LOOKUP would work?

Also - I copied these two sheets to a new workbook that I am attaching (which may have broken links) it is just to help clarify. in Sheet2, range("AR5:AR47") is what I want copied to Sheet1 to the "1st QTR" (which you will see in row 5) of the year chossen in the combo box.

I hope this is not too confusing and THANKS AGAIN - Jim Ayers

**fredcho** · 01-29-2013, 06:10 AM

How do you make this code loop through all columns to the right?

Originally Posted by Leith Ross

Hello jayers,

This macro will copy the range only if both conditions are met. The CountA function will count formula that may not be visible and spaces in cells.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**arlu1201** · 01-29-2013, 06:27 AM

fredcho,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.