How to return the result of a cell's formula as an argument within a running macro?

How to return the result of a cell's formula as an argument within a
running macro?

Hi to All,

I'm sorting a table to extract data which becomes a new variable
array (variable number of rows but columns don't change and top left
is always the same cell address - in this case C7)

Alongside this array in cell R5 I have a formula which automatically
calculates the variable array dimensions. Let's say for example this
returns the text string C7:M32

The macro gets to the point of having done the sorting, the new data is
all nicely arranged in the variable array and cell R5 has updated
itself. Perfect so far!

I then want the macro to set the print area to the array dimensions
calculated in R5 and this is where I get stuck. I have found heaps of
examples on how macros can write data to a cell but nothing on how a
macro can read data from a cell to be used as an argument within the
macro. Perplexed. A great big "Thanks!" to anyone who can help.

Cheers,
RonW.

PS: please don't assume I know anything at all about VBA and macro
programming . . .

ActiveSheet.PageSetup.PrintArea = Range("R5").value

HTH
--
AP


"Worldman1" <[email protected]> a écrit dans le message de news:
[email protected]...
> How to return the result of a cell's formula as an argument within a
> running macro?
>
> Hi to All,
>
> I'm sorting a table to extract data which becomes a new variable
> array (variable number of rows but columns don't change and top left
> is always the same cell address - in this case C7)
>
> Alongside this array in cell R5 I have a formula which automatically
> calculates the variable array dimensions. Let's say for example this
> returns the text string C7:M32
>
> The macro gets to the point of having done the sorting, the new data is
> all nicely arranged in the variable array and cell R5 has updated
> itself. Perfect so far!
>
> I then want the macro to set the print area to the array dimensions
> calculated in R5 and this is where I get stuck. I have found heaps of
> examples on how macros can write data to a cell but nothing on how a
> macro can read data from a cell to be used as an argument within the
> macro. Perplexed. A great big "Thanks!" to anyone who can help.
>
> Cheers,
> RonW.
>
> PS: please don't assume I know anything at all about VBA and macro
> programming . . .
>

Great ! I just love those guys 'n gals who offer free help.
I do the same in areas where I have the knowledge.

Thanks, AP . . .

Hi to AP and All,

The procedure below adds a thick border to the bottom of my varaible
array.
I want to replace the fixed range "Range("C13:M14").Select" with the
variable
string returned by my formula in R4 using the same approach as in:

"ActiveSheet.PageSetup.PrintArea = Range("R5").value"

I'm having a problem to get the syntax right. More help please! /
thanks again :-)

Range("C13:M14").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End Sub

Range(Range("R4").Value).Select

Cheers
--
AP

"Worldman1" <[email protected]> a écrit dans le message de news:
[email protected]...
> Hi to AP and All,
>
> The procedure below adds a thick border to the bottom of my varaible
> array.
> I want to replace the fixed range "Range("C13:M14").Select" with the
> variable
> string returned by my formula in R4 using the same approach as in:
>
> "ActiveSheet.PageSetup.PrintArea = Range("R5").value"
>
> I'm having a problem to get the syntax right. More help please! /
> thanks again :-)
>
> Range("C13:M14").Select
> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
> Selection.Borders(xlEdgeLeft).LineStyle = xlNone
> Selection.Borders(xlEdgeTop).LineStyle = xlNone
> Selection.Borders(xlEdgeBottom).LineStyle = xlNone
> Selection.Borders(xlEdgeRight).LineStyle = xlNone
> Selection.Borders(xlInsideVertical).LineStyle = xlNone
> With Selection.Borders(xlInsideHorizontal)
> .LineStyle = xlContinuous
> .Weight = xlThick
> .ColorIndex = xlAutomatic
> End With
> End Sub
>