There is another approach that might be worth experimenting with.
As you say, Excel will include formulas that return blanks into a chart.
However, it will ignore cells that have #N/A value, even if they are the
result of a formula.
You can get this value by calling the NA() function in your IF, rather than
returning an empty string.
The down side of this is all the #N/A cells in the chart's source data, but
this isn't necessarily a problem, depending on what else that source data
area is used for.
Regards, Dave S
----- Original Message -----
From: "sspatriots" <sspatriots@yahoo.com>
To: <ExcelVBA@yahoogroups.com>
Sent: Thursday, April 26, 2012 10:58 PM
Subject: [ExcelVBA] Copy column range of formulas to next column
> Hi,
>
> I have a worksheet that has column headings by the month in the range
> 'B1:M1'. The first one is 'AUG' (B1), because that is the start of our
> fiscal year. Anyhow, right now I have formulas in the range 'B2:I40' that
> are populating data from another worksheet into that range. Columns 'B'
> through 'I' are the months from last 'AUG' through 'MAR', which is all I
> have data for at the moment. I don't want to put the formulas into
> 'J2:M40' until I get the data for the months of 'APR' through 'JUL' and I
> only want them added one month at a time as I obtain each months data.
>
> My reasoning for this is that this range of data gets fed into a bar chart
> with an exponential trendline. If those cells have anything in them (even
> a formula that makes them appear blank), the trendline option isn't
> available.
>
> So if I were to run the code today, I would want it to find the last range
> of formulas for the previous month's data (currently in 'I2:I40') and
> paste them into 'J2:J40' just before I bring my new data into the workbook
> for 'APR'. I hope this makes sense and that I've explained it clearly.
>
> Thanks in advance for any suggestions/ideas, because I've been scouring
> the web and not able to come up with anything close.
>
>
> Regards,
>
> Steve
>
>
>
> ------------------------------------
>
> ----------------------------------
> Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go
> here: http://www.mousetrax.com/techtrax to enter the ezine, then search
> the ARCHIVES for EXCEL VBA.
>
> ----------------------------------
> Visit our ExcelVBA group home page for more info and support files:
> http://groups.yahoo.com/group/ExcelVBA
>
> ----------------------------------
> More free tutorials and resources available at:
> http://www.mousetrax.com
>
> ----------------------------------Yahoo! Groups Links
>
>
>
>
Be sure to check out TechTrax Ezine for many, free Excel VBA articles! Go here: http://www.mousetrax.com/techtrax to enter the ezine, then search the ARCHIVES for EXCEL VBA.
----------------------------------
Visit our ExcelVBA group home page for more info and support files:
http://groups.yahoo.com/group/ExcelVBA
----------------------------------
More free tutorials and resources available at:
http://www.mousetrax.com
----------------------------------
Tidak ada komentar:
Posting Komentar