Help on syntax

T

Tom Stapleton

I am very new to VBA and am looking for any help with this problem. I will
try and be as clear as possible. First, here is the initial code.

Sub calc()
i = 35
x = 1
Do
Sheets("sheet1").Range("G97").Offset(x - 1, 0) = Sheets("sheet1").
Range("F97").Value
Sheets("sheet1").Range("H97").Offset(x - 1, 0) = Sheets("sheet1").
Range("F98").Value
Sheets("sheet1").Range("B97") = i - x + 1
x = x + 1
Loop Until i - x + 1 < -35
End Sub

This works very well for my needs except for one problem. The syntax uses
fixed cell locations. The macro uses Cell B97 and changes its value from 35
to -35, one number at a time. Each time the number changes, the values
produced in Cells F97 and F98 are placed in Cells G97 and H97.
Could someone show the syntax that in effect would cause the macro to run
from a selected cell rather than a fixed cell (B97) and also keep the other
cells relative as far as position? I ask this because each day I have to run
the macro on the Cell immediately below the Cell I used the day before (use
B97 today, use B98 tomorrow, use B99 the following day, etc) Having to
manually change the Cell Ranges in the code is very time consuming. Any help
would be much appreciated.
 
B

Bob Phillips

Untested

Sub calc()
i = 35
x = 1
Set rng = Sheets("sheet1").Activecell
Do
rng.Offset(x - 1, 5) = rng.Offset(0,4).Value
rng.Offset(x - 1, 6) = rng.Offset(1,4).Value
rng.Value = i - x + 1
x = x + 1
Loop Until i - x + 1 < -35
End Sub

Bit odd though, why overwrite the activecell, or B97 in your exampole,
ever5y iteration of the loop?

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Tom Stapleton

Thanks Bob. I will test it. Once I get the data, I restore the cell to its
original value (B97). I just need the data
the macro provides for another area.
 
T

Tom Stapleton

Bob, I get a runtime error, but thanks anyway. Ian's code is almost there.
Thanks again for your time.
 
Top