Help on syntax

[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.

 

[Ian]

Not fully tested (ie zero values everywhere in the sheet initially), but
this should work. In your example, I selected B97 and ended up with -35 in
the cell. I then selected B99 and got -35 there.

Sub calc()
a = ActiveCell.Offset(0, 5).Address
b = ActiveCell.Offset(0, 4).Address
c = ActiveCell.Offset(0, 6).Address
d = ActiveCell.Offset(1, 4).Address
e = ActiveCell.Address

i = 35
x = 1
Do
Sheets("sheet1").Range(a).Offset(x - 1, 0) =
Sheets("sheet1").Range(b).Value
Sheets("sheet1").Range(c).Offset(x - 1, 0) =
Sheets("sheet1").Range(d).Value
Sheets("sheet1").Range(e) = i - x + 1
x = x + 1
Loop Until i - x + 1 < -35
End Sub

 

[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)

 

[Tom Stapleton]

Thanks loads Ian. I will test it and see what happens

 

[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.

 

[Tom Stapleton]

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

 

[Tom Stapleton]

Ian, I ran it and it worked just fine. However I gave you a wrong turn. I
forgot to mention that the location of where to put the data was different.
Probably an easy fix for you (not for me). I changed the code to start
filling the data at Cells B175 and C175. It filled it alright except it was
175 rows down. I want to keep the beginning of the fill location at B175 and
C175 no matter which Cell I select to start the macro. But if I were to
start, in say Cell M97, I would want it to fill starting at M175. Again
keeping all activity relative. Confusing you yet? Here's what I did.....

Sub calc()
a = ActiveCell.Range("B175").Address
b = ActiveCell.Offset(0, 4).Address
c = ActiveCell.Range("C175").Address
d = ActiveCell.Offset(1, 4).Address
e = ActiveCell.Address

i = 35
x = 1
Do
Sheets("sheet1").Range(a).Offset(x - 1, 0) = Sheets("sheet1").
Range(b).Value
Sheets("sheet1").Range(c).Offset(x - 1, 0) = Sheets("sheet1").
Range(d).Value
Sheets("sheet1").Range(e) = i - x + 1
x = x + 1
Loop Until i - x + 1 < -35
End Sub

 

[Ian]

I have to admit to being confused. Are you saying that if you select any
cell in eg column B you want to start filling at B175, but if the selected
cell is in eg column F you want to start at F175? ie you want the fill to be
relative to the column, but not the row?

 

[Tom Stapleton]

Ian, I apologize for not being clearer. I'll try to be more thorough.
In the code you gave me, as you probably know, when Cell B97
was selected and the macro was run, the output data started
to fill in at Cells G97 and H97 and continued down until it finished. What I
need it to do is start filling the output data at Cells B175 and C175
instead of Cells G97 and H97.
Each day as I move down the sheet one cell and run the macro again, I want
it to fill in the same location.
If I move to another column that I need to run the macro in,
say Column M for example, and I select Cell M99 for example,
then run the macro, I need the output data to start filling
at Cells M175 and N175. I hope this better relates my request. Thanks in
advance Ian.

 

[Ian]

I'm afraid I'm still confused.

Editing your original posting with hard coded values,, please can you
comment on my assumptions?

Assuming the active cell is D99:

Sheets("sheet1").Range("I99").Offset(x - 1, 0) =
Sheets("sheet1").Range("D175").Value
Assuming source is column offset by 5,activecell row. Assuming destination
is activecell column, row 175.
Sheets("sheet1").Range("J99").Offset(x - 1, 0) =
Sheets("sheet1").Range("D176").Value
Assuming source is one column right of the source in the line above Assuming
destination is one cell below the destination in the line above.
Sheets("sheet1").Range("B97") = i - x + 1
Assuming activecell.

If this isn't the case, can you copy back a couple of examples using
different active cells (differing row and column).

 

[Tom Stapleton]

Ian, thanks for your time. I respect it. But I got the problem solved
elsewhere. Take care.

 

[Ian]

Glad you got it sorted.