Placing a regular excel function in VBA code [HOW?]
 

Hi everyone.
Im sorry but Im totally illiterate in VBA and have very little
experience in macro (hours only ;). I recorded a macro containing the
following code:

Sub Macro4()
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$34"
End Sub

What I would like to accomplish is to change "$E$34" into a regular
Excel function:

="$E$" & (MATCH ("abc";A1:A40))

(the function looks for "abc" text in A column and returns the address
of the cell in E column in the same row)

Could someone tell me how it can be done? I was looking in Google for a
while but without success. Thanks in advance,
gordom

Placing a regular excel function in VBA code [HOW?]
 

gordom;263410 Wrote:
Hi everyone.
Im sorry but Im totally illiterate in VBA and have very little
experience in macro (hours only ;). I recorded a macro containing the
following code:

Sub Macro4()
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$34"
End Sub

What I would like to accomplish is to change "$E$34" into a regular
Excel function:

="$E$" & (MATCH ("abc";A1:A40))

(the function looks for "abc" text in A column and returns the address
of the cell in E column in the same row)

Could someone tell me how it can be done? I was looking in Google for a
while but without success. Thanks in advance,
gordom
Something like:

Sub Macro4()
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$" &
Application.WorksheetFunction.(MATCH
("abc",Activesheet.Range("A1:A40"))).Address
End Sub

Not tested but you get the idea!


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=73534

Placing a regular excel function in VBA code [HOW?]
 

Type in your function. turn on the macro recorder. Run through the sequence
you need (remember VBA is very linear, with a few exceptions such as calling
Subs, etc.). Click on the cell with your function as part of the sequence.
Turn off your macro recorder when you are done. Examine the code. I do this
all the time. When you click your function, with the Macro Recorder on, you
will get that function in VBA code. I just did it and got this:
ActiveCell.FormulaR1C1 = _
"=INDEX(R[-3]C[-7]:RC[-4],MATCH(R[-4]C[-2],R[-3]C[-8]:RC[-8]),MATCH(R[-4]C[-1],R[-4]C[-7]:R[-4]C[-4]))"

....your results will (almost certainly) be slightly different.

Regards,
Ryan---

--
RyGuy


"gordom" wrote:

Hi everyone.
Im sorry but Im totally illiterate in VBA and have very little
experience in macro (hours only ;). I recorded a macro containing the
following code:

Sub Macro4()
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$34"
End Sub

What I would like to accomplish is to change "$E$34" into a regular
Excel function:

="$E$" & (MATCH ("abc";A1:A40))

(the function looks for "abc" text in A column and returns the address
of the cell in E column in the same row)

Could someone tell me how it can be done? I was looking in Google for a
while but without success. Thanks in advance,
gordom

Placing a regular excel function in VBA code [HOW?]
 

Give the code a try...

Sub Macro4()
On Error Resume Next
PrintAddr = "$A$1:$E$" & Range("A1:A40").Find("abc").Row
If Err.Number = 0 Then
ActiveSheet.PageSetup.PrintArea = PrintAddr
Else
MsgBox "The text ""abc"" did not appear in the specified range!"
End If
End Sub

--
Rick (MVP - Excel)


"gordom" wrote in message
...
Hi everyone.
Im sorry but Im totally illiterate in VBA and have very little
experience in macro (hours only ;). I recorded a macro containing the
following code:

Sub Macro4()
ActiveSheet.PageSetup.PrintArea = "$A$1:$E$34"
End Sub

What I would like to accomplish is to change "$E$34" into a regular Excel
function:

="$E$" & (MATCH ("abc";A1:A40))

(the function looks for "abc" text in A column and returns the address of
the cell in E column in the same row)

Could someone tell me how it can be done? I was looking in Google for a
while but without success. Thanks in advance,
gordom

Placing a regular excel function in VBA code [HOW?]
 

W dniu 2009-03-10 17:43, Rick Rothstein pisze:
Give the code a try...

Sub Macro4()
On Error Resume Next
PrintAddr = "$A$1:$E$" & Range("A1:A40").Find("abc").Row
If Err.Number = 0 Then
ActiveSheet.PageSetup.PrintArea = PrintAddr
Else
MsgBox "The text ""abc"" did not appear in the specified range!"
End If
End Sub

It works super, thanks :-)