Basic macro problem

[dave_m]

Hi all,

I wonder if anyone can help with the small problem i'm having? I'm pretty new to VBA and am having some teething problems....
I'm trying to write a macro which takes a variable which i have already calculated earlier in the macro (VelocityOne), and adds it to the value in the cell above which the newly calculated variable needs to be written. This needs to be repeated for a number of rows. However, the number of rows for which this needs to be done is the value of another variable (intWorkingRowVelEnd). I'm not sure if this description makes any sense, so i've put the code below.

Thanks in advance,

Dave


=================================


ReDim i(intWorkingRowVelEnd) As Variant

Worksheets("Working").Cells(3, 1).Select

For i = 1 To intWorkingRowVelEnd

CFVel = VelocityOne + Cells(1 + i, 1)

Next i


For j = 1 To intWorkingRowVelEnd

Worksheets("Working").Cells(2 + j, 1).Value = CFVel

Next j

 

[mcgew]

At the moment you have two loops, so once you get to the 'j' loop, you'll only have one value fro CF Vel.
I'm not totally clear what you're trying to do - is it: start with VelocityOne in a cell1, then have cell1 +VelocityOne in the cell below (say cell2), cell2+velocityOne in the cell below(cell3) and so on for intWorkingRowVelEnd rows?

if so then try:

Cells(1,1).activate
Activecell.value = VelocityOne
Activecell.offset(1,0).activate
For i = 1 to intWorkingRowVelEnd
ActiveCell.value = activecell.offset(-1,0).value + VelocityOne
Activecell.offset(1,0).activate
Next i

 

[dave_m]

Hi again,

Thanks for the reply. You understood what i need exactly and i modified the code you wrote slightly (as i want it to start on row 2 rather than row 1), but there appears to be a problem with the line:

For i = 1 to intWorkingRowVelDepth

I get an error message saying that there is a Runtime error '438'. The object doesn't support this property or method. Any ideas?

Ta again,

Dave

 

[mcgew]

Try declaring your intWorkingRowVelDepth variable differently.
At the moment you've got:

ReDim i(intWorkingRowVelEnd) As Variant

Try replacing that with:
ReDim intWorkingRowVelEnd as Integer

for this to work, you have to make sure the intWorkingRowVelEnd is always an integer else you'll get an error.

depending on what type of number that variable is.

Grace

 

[dave_m]

Hi again,

Thanks for the reply, I've tried your suggestion and have ended up with the code as posted below....
Its appears now that, when it gets to the same For i =1 line, there is a compile error: Type mismatch

I have the varible i declared as an ineteger, and intWorkingRowVelEnd, is an ineteger throughout the macro.

Any further ideas?

Thanks again,

Dave


================


ReDim i(intWorkingRowVelEnd) As Integer

Cells(2, 1).Activate
ActiveCell.Value = VelocityOne
ActiveCell.Offset(1, 0).Active
For i = 1 To intWorkingRowVelEnd
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + VelocityOne
ActiveCell.Offset(1, 0).Activate
Next i

 

[mcgew]

You don't need to declare i as an integer - that's automatic when you're using For-to-next.
Replace: ReDim i(intWorkingRowVelEnd) As Integer
with: ReDim intWorkingRowVelEnd As Integer
ie. get rid of the i and the brackets.

Hope that helps

 

[dave_m]

Hi Again....

When I do what you suggested i.e. remove the brackets and the i, I get a syntax error and the code is highlighted in red.
Any further idea's/suggestions would be greatly appreciated.

Dave

 

[mcgew]

One thing is that on the third line of the code you've shown, you've got:
ActiveCell.Offset(1, 0).Active
that should be:
ActiveCell.Offset(1, 0).Activate

for the error you're getting, i'm not that familiar with redim, but from the code you've got there, it doesn't look like you need a dynamic array (unless you need it somehwere else).
you should be able to get away with:

Dim intWorkingRowVelEnd As Integer

Cells(2, 1).Activate
ActiveCell.Value = VelocityOne
ActiveCell.Offset(1, 0).Activate
For i = 1 To intWorkingRowVelEnd
ActiveCell.Value = ActiveCell.Offset(-1, 0).Value + VelocityOne
ActiveCell.Offset(1, 0).Activate
Next i

if this is within a loop and intWorkingRowVelEnd changes with each loop, you could add intWorkingRowVelEnd = whatever it's based on, otherwise the above should work as is.

 

[dave_m]

Thank you very much. It works a treat now.

Cheers again,

Dave