Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert a julian gregorian date code into a regular date | Excel Worksheet Functions | |||
regular code module | Excel Discussion (Misc queries) | |||
Placing a Return in a Function | Excel Discussion (Misc queries) | |||
placing NOW function in IF function | Excel Worksheet Functions | |||
possibility of automatically placing an autoshape using a function | Excel Programming |