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" a écrit dans le message de news:
...
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" a écrit dans le message de news:
...
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