correct formula syntax in macro

[ruler]

Hi,

The macro works but when I replace argument with formula it fails. How is this formula's correct syntax in macro?

'<< Open Folder >>
Sub OpenWindowsExplorer()

ActiveWorkbook.FollowHyperlink "F:\dm_06", NewWindow:=True
Exit Sub
End Sub

Above works.
When I replace "F:\dm_06" with =+HYPERLINK(+VLOOKUP(F7,mytable,2)) or with +VLOOKUP(F7,mytable,2), macro fails with function not supported message.

+VLOOKUP(F7,mytable,2) look up result is text F:\dm_06

Is it even possible, that such formula works in macro?

I have spent many hours with this task alreadyy :-(
Thanks for any help.

[mdmackillop]

You need to use
[VBA]
ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(Range("F7"), Range("MyTable"), 2)

[/VBA]

[ruler]

Thanks a lot! It works!
Almost done.

ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(Range("F7"), Range("MyTable"), 2)

Is it possible to replace also argument of fixed cell F7 with value of active cell (a text, value of present cell)? I have tried but appearently Active Cell can't be inserted directly.

Thanks.

[mdmackillop]

Haven't tried it, but this should work.
ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(ActiveCell, Range("MyTable"), 2)

[ruler]

Thanks, It works like a charm. You save my day. Sorry to misuse your time, but

Is there an equivalent to the ActiveCell, something like leftmost cell in the active row, which might work in this macro?
Purpose is to be able to run the macro from whatever column (cell) in the row, where argument is in the first column of each row.

Thanks

[mdmackillop]

No Problem
[vba] ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row,1), _
Range("MyTable"), 2)[/vba]