# Basic macro problem

#### dave_m

##### New Member
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.

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

### Excel Facts

Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### mcgew

##### Board Regular
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

##### New Member
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

##### Board Regular
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

##### New Member

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

##### Board Regular
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

##### New Member

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

##### Board Regular
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

##### New Member
Thank you very much. It works a treat now.

Cheers again,

Dave

Replies
3
Views
250
Replies
8
Views
174
Replies
2
Views
297
Replies
5
Views
143
Replies
2
Views
109

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,844
Messages
5,766,748
Members
425,378
Latest member
kapoor2892

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back