PDA

View Full Version : correct formula syntax in macro



ruler
02-09-2007, 05:49 AM
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
02-09-2007, 06:29 AM
You need to use

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

ruler
02-09-2007, 08:49 AM
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
02-09-2007, 09:38 AM
Haven't tried it, but this should work.
ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(ActiveCell, Range("MyTable"), 2)

ruler
02-09-2007, 11:01 AM
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
02-09-2007, 11:09 AM
No Problem
ActiveWorkbook.FollowHyperlink Application.WorksheetFunction.VLookup(Cells(ActiveCell.Row,1), _
Range("MyTable"), 2)