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

[gordom]

Hi everyone.
I’m sorry but I’m 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

 

[Simon Lloyd]

Hi everyone
I’m sorry but I’m totally illiterate in VBA and have very littl
experience in macro (hours only . I recorded a macro containing th
following code

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

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

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

(the function looks for "abc" text in A column and returns the addres
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
while but without success. Thanks in advance
gordo

Something like

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

Not tested but you get the idea

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[ryguy7272]

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

 

[Rick Rothstein]

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

 

[gordom]

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