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]

There is no paste command.

I'm copying C1 and specifying the destination, that's how the Copy method works.

Does the code work if you use your loop?

 

[shodan]

Norie, yes it works now , but maybe somewhat slow. this is what i did:

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 itemcounter
Cells(i, 3).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A" & i & ",(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))" ' this formula is to be replaced by the large sumif formula
Next i

End Sub

ways for improvement?

 

[Norie]

I really can't see why my Copy method didn't work.

Does this work?

Range("C1:C" & itemcounter).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=Printout!A1,(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))"

 

[shodan]

norie,

I did this now

Sub norie()

Dim itemrange As Range
Dim itemcounter As Integer


Worksheets("VOLUMES").Range("ITEMS").Copy
Worksheets.Add
With ActiveSheet
    .Paste
    .Name = "PrintoutN"
    Set itemrange = .Range("A1").CurrentRegion
    
    itemcounter = itemrange.Rows.Count
    .Range("C1:C" & itemcounter).FormulaArray = "=SUM(IF(DATABASE!$E$4:$AJ$10000=PrintoutN!A1,(DATABASE!$C$4:$C$10000)*(DATABASE!$F$4:$AJ$10000)))"
    '.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

End Sub

but still get the same error saying that i can not change a part of an array !

edit, but despite of the error, i see that it has carried out the procedure. but the A1 is not variable. it is a lot faster, but that **** error.

strange... to be continued??

gonna go for a beer now , cheers

 

[Norie]

Shodan

Is it just a typo but you still have the ; in the formula?

I don't know if that will help.

Maybe you'll just have to stick with the loop to put the formulas in, perhaps VBA has some 'issues' with array formulas.

 

[shodan]

that is the old formula which is put as comment.

the strange thing is though that your procedure ends with an error , but is carried out untill the last l

 

[Norie]

that is the old formula which is put as comment.

My mistake!

the strange thing is though that your procedure ends with an error , but is carried out untill the last l

What do you mean?

 

[shodan]

Well, if I carry out your procedure, it runs it for my 152 items, calculated with the itemcounter and than it ends with the error on the last line, being the resize thing. nevertheless, if I look on my sheet than, I see that the formula is entered . and it also gives back the result. the only thing is that the printout!A1 is not variable.