Store cell values in an array and return values on specific condition

**gmalpani** · 11-26-2011, 05:03 PM

Hi All,

I need help in writing a macro which can perform following actions when cell value is YES or NO.

I have three worksheets in workbook - "Sheet1, Sheet2 and Sheet3"

- In Sheet1, I have given a condition "YES" or "NO" in cell "A1".

If cell "A1" in Sheet1 is "Yes". Perform following actions in Sheet 2 and Sheet 3:

1. From G14 to G18 and H20 to H24: Preserve/Store the existing values in an array and then set values to 0.

2. Hide Column G and H

3. Hide Rows 14 to 18 and Rows 20 to 24.

Else IF A1 in Sheet1 is "No".

1. Return values in respective cells which are stored in Array
2. Unhide Columns
2. Unhide Rows

I tried to write a VBA code for this but there were many errors. Below is the code I tried:

Please Login or Register  to view this content.

**pike** · 11-26-2011, 11:19 PM

hi gmalpani
"Do Whlie"

needs a "Loop" at the end
and the array wont be Preserve/Store of the next worksheet event .

you will have to store the values in another sheet for preserving

**gmalpani** · 11-27-2011, 04:38 AM

Hi,
Thanks for the response !!

Now I have tried to store values in the same worksheet but at different cell location.

Now, following actions has to be done:

IF Sheet1, Cell A1 is "YES", Then:

In Sheet 2 and Sheet 3:
- Copy Cell values (paste special values) from G14:G18 to G29:G33
- Set values of Cell G14:G18 to 0.
- Copy Cell values (paste special values) from H20:H24 to H29:H33
- Set values of Cell H20:G24 to 0.
- Hide rows from 14 to 18 and 20 to 24
- Hide Columns G:H

Else IF Sheet1, Cell A1 is "NO", Then:
In Sheet 2 and Sheet 3:
- Unhide Rows 14 to 18 and 20 to 24
- Unhide Columns G:H
- Copy Cell values (paste special values) from G29:G33 to G14:G18
- Copy Cell values (paste special values) from H29:H33 to H20:H24.

I tried to write a code for this but end up with an error. As I am a beginner in excel, I request your support to debug this code.

Please Login or Register  to view this content.

**pike** · 11-27-2011, 05:01 AM

hi gmalpani
very good effort
I dont exactly follow what goes on what sheet
this code with copy and destination

Please Login or Register  to view this content.

can you explain or expand more?

**gmalpani** · 11-27-2011, 05:39 AM

Hi Pike,

For specific condition YES/NO, I have to perform similar actions in Sheet2 and Sheet 3. Because in the original file I am working on the only difference between Sheet 2 and Sheet 3 is in the values of G14 to G18 and G20 to G24.

That's why I have used Sheet Array -

Please Login or Register  to view this content.

Here is more detailed flow:

IF Sheet1, Cell A1 is "YES", Then Perform following similar actions in both Sheet 2 and Sheet 3.

- Copy Cell values (paste special values) from Sheet 2(G14:G18) to Sheet 2 (G29:G33) and Sheet 3(G14:G18) to Sheet 3 (G29:G33)
- Set values of Cell Sheet 2 (G14:G18) to 0 and Sheet 3 (G14:G18) to 0.
- Copy Cell values (paste special values) from Sheet 2 (H20:H24) to Sheet 2 (H29:H33) and Sheet 3 (H20:H24) to Sheet 3 (H29:H33)
- Set values of Cell Sheet 2 (H20:G24) to 0 and Sheet 3 (H20:G24) to 0.
- Hide Sheet 2 (Row 14 to 18) and Sheet 2 (Row 20 to 24). Similarly, Hide Sheet 3 (Row 14 to 18) and Sheet 3 (Row 20 to 24)
- Hide Sheet 2 (Columns G:H) and Sheet 3 (Columns G:H)

Else IF Sheet1, Cell A1 is "NO", Then:
In Sheet 2 and Sheet 3:
- Unhide Sheet 2 (Row 14 to 18) and Sheet 2 (Row 20 to 24). Similarly, Unhide Sheet 3 (Row 14 to 18) and Sheet 3 (Row 20 to 24)
- Unhide Sheet 2 (Columns G:H) and Sheet 3 (Columns G:H)
- Copy Cell values (paste special values) from Sheet 2 (G29:G33) to Sheet 2 (G14:G18) and Sheet 3 (G29:G33) to Sheet 3 (G14:G18)
- Copy Cell values (paste special values) from Sheet 2 (H29:H33) to Sheet 2(H20:H24) and Sheet 3 (H29:H33) to Sheet 3(H20:H24).

Also, while copying I want only values to be pasted. I mean I have to paste special for copying values and not the formulas behind that.

**pike** · 11-27-2011, 06:08 AM

Hey gmalpani
Think im getting closer

hmmm. try

Please Login or Register  to view this content.

we will get there

**gmalpani** · 11-27-2011, 06:22 AM

Hi Pike,

Thanks a lot !! This works exactly the way I want

Br,
gmalpani

**pike** · 11-27-2011, 06:43 AM

your welcome yay !

Store cell values in an array and return values on specific condition

LinkBack

Thread Tools

Rate This Thread

Display

Store cell values in an array and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Store cell values on different cell location and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Re: Store cell values in an array and return values on specific condition

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions