Prev: Complex If/Or/And formula
Next: How to skip the confirmation on updating link?
From: cferoiu on 26 Mar 2010 18:28
I have 4 cells: H8 I8 J8 K8
If change H8, I8 J8 K8 cells have variable value.
I want to change values of H8 from 10 to 150 and collect all 4 cells value
in columns AF AG AH AI.

Please help me with same VBA code. Thanks.
From: B Lynn B on 26 Mar 2010 19:12
Since you didn't specify where to put the collected values, this will drop
them into range A1:D141. Of course you'll want to modify to put them where
you really want them:

Sub myCalcsCollector()

Dim myNum as long

For myNum = 10 to 150
Range("H8") = myNum
application.calculate
cells(myNum - 7, 1) = myNum
cells(myNum - 7, 2) = range("I8")
cells(myNum - 7, 3) = range("J8")
cells(myNum - 7, 4) = range("K8")
Next myNum

If automatic calculation is turned on, then of course you don't need the
line to force calculation.

"cferoiu" wrote:

> I have 4 cells: H8 I8 J8 K8
> If change H8, I8 J8 K8 cells have variable value.
> I want to change values of H8 from 10 to 150 and collect all 4 cells value
> in columns AF AG AH AI.
>
> Please help me with same VBA code. Thanks.
From: B Lynn B on 26 Mar 2010 19:17
Oh, sorry, you did say the columns you wanted to put them in. Below is the
same thing, but assumes starting at row 8.

"B Lynn B" wrote:

> Since you didn't specify where to put the collected values, this will drop
> them into range A1:D141. Of course you'll want to modify to put them where
> you really want them:
>
> Sub myCalcsCollector()
>
> Dim myNum as long
>
> For myNum = 10 to 150
> Range("H8") = myNum
> application.calculate
> cells(myNum, 32) = myNum
> cells(myNum, 33) = range("I8")
> cells(myNum, 34) = range("J8")
> cells(myNum, 35) = range("K8")
> Next myNum
>
> If automatic calculation is turned on, then of course you don't need the
> line to force calculation.
>
> "cferoiu" wrote:
>
> > I have 4 cells: H8 I8 J8 K8
> > If change H8, I8 J8 K8 cells have variable value.
> > I want to change values of H8 from 10 to 150 and collect all 4 cells value
> > in columns AF AG AH AI.
> >
> > Please help me with same VBA code. Thanks.
From: Shane Devenshire on 26 Mar 2010 23:46
Hi,

What exactly do you mean by "collect all 4 cell values"?
Where in columns AF:AI do you want to put them? (What row?)
What do you mean by change values of H8 from 10 to 150? H8 is only one cell?

Let's suppose you mean you want to copy the entries in H8:K8 to AF8:AI8 then
this code will do it:

[AF8:AI8] = [H8:K8].Value


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"cferoiu" wrote:

> I have 4 cells: H8 I8 J8 K8
> If change H8, I8 J8 K8 cells have variable value.
> I want to change values of H8 from 10 to 150 and collect all 4 cells value
> in columns AF AG AH AI.
>
> Please help me with same VBA code. Thanks.
 | 
Pages: 1
Prev: Complex If/Or/And formula
Next: How to skip the confirmation on updating link?