long formulas restricted?

[jus]

Hi I am a newbie to this but this is my problem.

This is the problem line after testing each line out, the error is caught here.
Worksheets("OS for UK").Range(globalOSlastcolumn & row).Formula = "=sum(" & x & ")"

x is a long line cells and is declared as a string:

F7,L7,R7,X7,AD7,AJ7,AP7,AV7,BB7,BH7,BN7,BT7,BZ7,
CF7,CL7,CR7,CX7,DD7,DJ7,DP7,DV7,EB7,EH7,EN7,ET7,
EZ7,FF7,FL7,FR7,FX7,GD7,GJ7

This gives me an error.

I have replaced with this line:
Worksheets("OS for UK").Range(GP7).Formula = "=sum(" & " F7,L7,R7,X7,AD7,AJ7,AP7,AV7,BB7,BH7,BN7,BT7,BZ7,CF7,CL7,CR7,CX7,DD7,DJ7,DP7,DV7,EB7,EH7,EN7,ET7,EZ7,FF7,FL7,FR7,FX7,GD7,GJ7 " & ")"

still error. Then i try this:
Worksheets("OS for UK").Range(GP7).Formula = "=sum(" & " F7,L7,R7" & ")" and its ok. I am just trying to test where the problem is.

Now I think its because of the long list of cells thats causing this. Do u have such experiences and how to solve this? Becasuse each cell for me is important, as it sums the total quantity of a product for me in each purchase order.

Actually i dont recall facing this problem previously. Could it be that after Microsoft updated their excel, that i lack something?

Lost dont know how to solve. If u can help me would be great thanks alot
cheers
jus

 

[Weaver]

Is it possible to have a row somewhere in the sheet containing 1s in the columns you want to add and zeros in the cols you don't?

Say it was row 1

You could then make your formula

=sumproduct(F1:GJ1,F7:GJ7)

so if the problem is down to an overly long formula this would get round that issue.

still error. Then i try this:
Worksheets("OS for UK").Range(GP7).Formula = "=sum(" & " F7,L7,R7" & ")" and its ok. I am just trying to test where the problem is.

Actually, I jsut noticed, GP7 should have quotes around it.

this works:

Sub test()
Worksheets("OS for UK").Range("GP7").Formula = "=sum(F7,L7,R7,X7,AD7,AJ7,AP7,AV7,BB7,BH7,BN7,BT7,BZ7,CF7,CL7,CR7,CX7,DD7,DJ7,DP7,DV7,EB7,EH7,EN7,ET7,EZ7,FF7,FL7,FR7,FX7)"
End Sub

I just deleted elements until it stopped giving errors, which would suggest it's currently too long.

 

[repairman615]

I think the fault is elsewhere. Perhaps in:

Worksheets("OS for UK").Range(globalOSlastcolumn & row).


A test shows this works:

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> a()<br><br><SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>x = "F7,L7,R7,X7,AD7,AJ7,AP7,AV7,BB7,BH7,BN7,BT7,BZ7,CF7,CL7,CR7,CX7,DD7,DJ7,DP7,DV7,EB7,EH7,EN7,ET7,EZ7,FF7,FL7,FR7,FX7,GD7, GJ7"<br><br>    ActiveCell.Formula = "=Sum(" & x & ")"<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[repairman615]

What is the error description and on what line??

 

[jus]

Hi guys thanks for attending to my question. The syntax works. I have tested it by replacing with a shorter line of formula. So i dont think the semi colons will help.

Repairman, the line with the problem is Worksheets("OS for UK").Range(globalOSlastcolumn & row).Formula = "=sum(" & x & ")"

when i commented (") this line, there is no error. Just that the formula does not get inserted into the cell.

I have error catching hence i know where i caught the error. I have removed it
and the error is "400"

I am not able to group the formula unfortunately as u can see, i need only to total up certain columns.

I tried once more, if the number of orders I have isless, it works perfectly. the number of orders will determine the length of that forumla string ie F7, L7, etc etc.

any ideas?

 

[repairman615]

.Range(globalOSlastcolumn & row).

try adding this before the above line... see what these variables are producing.

msgbox globaloslastcolumn
msgbox row
msgbox globalOSlastcolumn & row

I am not an VBA expert by any stretch, however if you are using "row" as a variable, that seems like a bad idea to me as row has programming properties.