Basic macro problem

dave_m

New Member
Joined
Jan 7, 2005
Messages
10
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mcgew

Board Regular
Joined
Sep 4, 2002
Messages
75
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
Joined
Jan 7, 2005
Messages
10
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
Joined
Sep 4, 2002
Messages
75
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
Joined
Jan 7, 2005
Messages
10

ADVERTISEMENT

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
Joined
Sep 4, 2002
Messages
75
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
Joined
Jan 7, 2005
Messages
10

ADVERTISEMENT

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
Joined
Sep 4, 2002
Messages
75
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.
 

Forum statistics

Threads
1,147,623
Messages
5,742,209
Members
423,714
Latest member
ftp2jz

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top