Dim integer in For loop does not work, why?

Hello,

I dimensioned Jan as Integer and made it = 31

In a For loop I said: For j=1 to Jan <--Does not work! The macro does not even run!
When I say instead: For j=1 to 31 <--Does work! The macro runs and everything works!

Why is that?

hi 123wc, can you post your code?

Basing on your explanation the following code runs without any troubles :

Please Login or Register  to view this content.

The only way for the code not to enter the loop is to comment out this line of the code:

Please Login or Register  to view this content.

thus making variable Jan being equal to zero.

Thanks man, I found the problem: I was declaring many variable in one line as this

Dim Jan, Feb, Mar....as Integer <--wrong

This is the correct way it seems: Dim Jan as Integer, Feb as Integer, Mar as Integer.... <--correct

You have identified the correct way to declare the variables (otherwise they end up being declared as Variants). however, I would be surprised if this alone was responsible for causing your loop not to run. What was your entire code before you fixed it?

The other mistake I did is that I initialized the variables the incorrect way too. eg:

Jan, Mar, May...=31 <--wrong
Jan=31: Mar=31: May=31 <--correct

Ah I see - thank you for the clarification

I don't want to start a new thread, so can you help me with summation?

Here is what I want to do:

X5 = SUM(G58:N58)
X5_offset1 = SUM(G58 +i: N58 +i) 'So this becomes G59:N59
X5_offset2 = SUM(G58 +i: N58 +i) 'So this becomes G60:N60

I know how to do the offset, and I know how to declare i and increment it. I just don't know how to do the summation in VBA! I also want the cells (X5, or whatever) to display the whole summation formula, ie:
= SUM(G58: N58)
= SUM(G59: N59)
etc.

I don't want the cells to display the summation value only.

Any ideas?

Thanks a lot

Sounds to me like you just need:

Please Login or Register  to view this content.

Adjust the X5:X10 to be the whole range you want to enter the formula into. Excel will automatically adjust the SUM() formula ranges to reflect the increasing rows

That works too. Thanks a lot

But sometimes I can't tell easily how far the loop is going so it's easier to just put the summation in the loop and it adds as the loop goes on.

Originally Posted by 123wc

That works too. Thanks a lot

But sometimes I can't tell easily how far the loop is going so it's easier to just put the summation in the loop and it adds as the loop goes on.

You can use the FormulaR1C1 property instead:

Please Login or Register  to view this content.