Solved: using just the cells with relevent data in to plot graph...

[divingdingo]

Another week, and another few questions for the experts!!

through VBA code i have used (kindly given to me via this forum, thankyou) a table is produced for my wind data which gives monthly averages.

only in certain months the formulae brings an error because there is no data.

i'm wondering how i should write VBA code so that i can just plot the averages of the months which have data in.

i've attached a sheet with a copy of what my tables look like.

can anyone suggest how i should elliminate the months which don't have data in please?

regards

mark

[Bob Phillips]

How about this?


__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

[divingdingo]

xld,

i'm not even testing you am i ?!!

that seems to be perfect, in fact it's better than what i was thinking.

thankyou kindly.

mark

[divingdingo]

hmmmm.

i've tried to copy this formula into VBA code;


'copy and paste special just the values
Range("C2:C19").Select
Selection.Copy
Range("D2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

'elliminating the error cells
Range("E2").Select
ActiveCell.FormulaR1C1 = "=IF(ISERROR(D2),NA(),D2)"
Range("E2").Select
Selection.AutoFill Destination:=Range("E2:E19")


but when it runs then i get an error msg.

also i just tried to imitate your formulae into your example, and it tells me that i have too many arguments for this type of function..

i think it might be time to get out my monday brick wall and !!!

[Bob Phillips]

[vba]

With ActiveSheet

.Columns(3).Insert
.Range("C1").Value = "Average Plot"
.Range("C2").Formula = "=IF(ISERROR(D2),NA(),D2)"
.Range("C2").AutoFill Destination:=Range("C2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1)
.Columns(3).NumberFormat = .Columns(4).NumberFormat
End With
[/vba]

[divingdingo]

i'd hate to be a nag!!, but every time i enter the above codes i have a complie error

the dreaded yello highlighter comes out and aparently the identifier under thte cursor is not recognised. (the cursor is on the formula)

i'm perplexed as i've retyped everything exactly as you have (double,tripple and quadrupple checked it).

i understand the code; If there is an error in b2, then input NA, else leave b2. but can't understand why my macro wont get past it.

[Bob Phillips]

Does this work

[vba]

With ActiveSheet

.Columns(3).Insert
.Range("C1").Value = "Average Plot"
.Range("C2").Formula = "=IF(ISERROR(D2),NA(),D2)"
.Range("C2").AutoFill Destination:=.Range("C2").Resize(.Cells(.Rows.Count, "A").End(xlUp).Row - 1)
.Columns(3).NumberFormat = .Columns(4).NumberFormat
End With
[/vba]

[divingdingo]

.Range("C2").Formula = "=IF(ISERROR(D2),NA(),D2)"

[/vba]

no but this does:


.Range("C2").Formula = "=IF(ISERROR(D2),NA(),(D2))"

do i get a gold star??!!!!!

seriously though xld, thanks very much for your help. i'm learning loads from this forum and yourelf.

[Bob Phillips]

Well I am totally confused. What you have added is totally superfluous, and I don't need it to make it work here. Whatever works I guess?

[divingdingo]

how odd, as on my computer those two brakets mean the sub either runs or doesn't !!

this VBA, will i ever understand it??? (no need to answer that!)

[Bob Phillips]

On the worksheet try adding

=IF(ISERROR(D2),NA(),D2)

and then

IF(ISERROR(D2),NA(),(D2))

and see if the first woirks, it should.