vba error?

[shodan]

I have a problem wiht a small vba code:

Sub Print_All_Items()
Dim itemrange As Range
Dim itemcounter As Integer

Worksheets("VOLUMES").Activate
Worksheets("VOLUMES").Range("ITEMS").Select
Selection.Copy
Worksheets.Add
With ActiveSheet
.Paste
.Name = "Printout"
End With

Set itemrange = Range("A1").CurrentRegion
itemcounter = itemrange.Rows.Count

Range("C1").Select
For i = 1 To 3
Cells(i, 3).Formula = "=sum(1+1)"  this formula is to be replaced by the large sumif formula
Next i


Range("C1").Formula = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A1;(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))"

The simple sum formula ( to test my code) needs to be replaced by the large sumif formula and I can't get it to work. Don't know why?

thanks for the help again.

 

[Norie]

Is that SUMIF formula an array formula?

Sub Print_All_Items()
Dim itemrange As Range
Dim itemcounter As Integer


Worksheets("VOLUMES").Range("ITEMS").Copy
Worksheets.Add
With ActiveSheet
    .Paste
    .Name = "Printout"
    Set itemrange = .Range("A1").CurrentRegion
    
    itemcounter = itemrange.Rows.Count

    .Cells(1, 3).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A1;(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))"
    .Cells(1, 3).Copy .Cells(1, 3).Resize(3, 3)
    
End With

 

[shodan]

Nori,
Indeed it is an Array formula. I tried that .formulaArray too.

the solution doesn't seem to work either. now i got the error msg:" unable to set the formula array property of the range class"

another thing, is there a reason that you removed the next/foor loop? i was using this loop to put the formula in the complete range of items.

Any other suggestions to get this code to work?

Thanks a lot !

 

[Richard Schollar]

I don't believe you are using a valid array formula because the range in the condition part is not the same size as the range to be summed.

Norie removed the For... Next bit simply because it is faster to copy the formula.

Regards

Richard

 

[shodan]

Parsnip,

It should be valid I guess, because I copied it from my worksheet, where it worked,.. but i'll check it again. Could it be that there is anything else wrong?

 

[Norie]

shodan

What's the seperator in your version of Excel?

In VBA you must use the American/English seperators when working with formulas.

Try replacing the semi-colon ; with a comma , .

When I do that the code doesn't cause any errors and the formula is entered on the sheet.

Obviously since I don't have the same set up as you I don't know if the formula is working properly, it's just returnin #N/A.

By the way were you going to use the itemcounter variable in your loop?

 

[shodan]

Norie, thanks, I believe indeed that will be it. I'm working with the semicolom and now I remember i had this error once before.

I'm new to vba, (you probably see this by my style of coding !!)) and I want to understand what i'm doing to lear you know. copying someonce code, even if it is better, is something i only want to do when i understand the code. otherwise it can turn against you sometime isn't that true? therefore my question concerning the next/for loop. the items counter count my number of items and to make sure that every item has a formula behind it, i was planning to use this next/for loop to place the formula via a loop in all my items. but maybe that is not good, (didn't yet come that far to test because of the error). The part "Printout!A1" must be variable so I will try your code now, but can you please explain me that last part than?

.Cells(1, 3).Copy .Cells(1, 3).Resize(3, 3)

thanks a lot for your help !!

 

[shodan]

Norie,

Formula works now. but using your code

code:
--------------------------------------------------------------------------------
.Cells(1, 3).Copy .Cells(1, 3).Resize(3, 3)
--------------------------------------------------------------------------------
I get the error that I can not resize an arry.

So I will have to use my orignal code than, Only I have to make sure that I make it variable with isn't it yet.

I will try to figure this out. But, if you think that using the next/for is not that good , please advice me. the only thing i want to do is copy the formula down to the last item.

Thanks for your help on this !!

 

[Norie]

Shodan

This might actually be an error on my part.

What the code does is copy the first cell with the formula, with the destination being a range which is defined using Resize.

Basically what Resize does is take a range and expand it by how many columns and rows you specify.

In my code I've expanded the range to 3 rows and 3 columns, which is incorrect. It should be 3 rows and 1 column.

Does this work?

itemcounter = itemrange.Rows.Count 

    .Cells(1, 3).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A1;(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))" 
    .Cells(1, 3).Copy .Cells(1, 3).Resize(itemcounter, 1)

 

[shodan]

Ok, indeed , that is where the itemcounter came in!! but it still gives me the error that i can not change a part of an array ??

question, about that last line... where is the paste command than? because I think that with the resize you are trying to define the paste range not?

regards,
shodan