I need excel VBA Macro expert
Budget: $10 – $30 USD
Building a VBA Macro - hotkey through cell references
Complete the VBA macro.
Make sure when the hotkey (ctrl+[) is pressed it iterates and Goes To all cell references in the active cell formula. And should return to the original cell.
For example, if you are in cell Sheet1!A1 which contains the formula "=C14", the first press of ctrl+[ should take you to Sheet1!C14. The second press should return you to cell A1. Needs to handle more complicated formulas such as "=VLOOKUP(3,I3:J6,2,FALSE)". See ExtractCellRefs in Additional Information.
Think I have most of the parts. Just need to increment to next reference upon hotkey click.
'''Function ExtractCellRefs(Rg As Range) As String
'Update by Extendoffice
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String
Application.Volatile
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s[].]{1,99})?'?!?$?[A-Z]{1,3}$?[0-9]{1,7}(:$?[A-Z]{1,3}$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
ExtractCellRefs = Left(xRet, Len(xRet) - 2)
Else
ExtractCellRefs = "No Matches"
End If
End Function
Sub GoToReferences()
Application.OnKey "+[", "ClickThroughReferences"
End Sub
Sub ClickThroughReferences()
Dim arr As Variant
arr = Split(ExtractCellRefs(ActiveCell.Value), ",")
Application.Goto Reference:=arr
End Sub'''
I think I'm only missing one small piece of code
just want to create hotkey (ctrl+[) that Goes To the cell references of a cell formula
don't think it would be more than 30 min of work for a VBA expert.
as a deliverable, I just want the code in text form, not in an excel workbook.
thanks.
Complete the VBA macro.
Make sure when the hotkey (ctrl+[) is pressed it iterates and Goes To all cell references in the active cell formula. And should return to the original cell.
For example, if you are in cell Sheet1!A1 which contains the formula "=C14", the first press of ctrl+[ should take you to Sheet1!C14. The second press should return you to cell A1. Needs to handle more complicated formulas such as "=VLOOKUP(3,I3:J6,2,FALSE)". See ExtractCellRefs in Additional Information.
Think I have most of the parts. Just need to increment to next reference upon hotkey click.
'''Function ExtractCellRefs(Rg As Range) As String
'Update by Extendoffice
Dim xRetList As Object
Dim xRegEx As Object
Dim I As Long
Dim xRet As String
Application.Volatile
Set xRegEx = CreateObject("VBSCRIPT.REGEXP")
With xRegEx
.Pattern = "('?[a-zA-Z0-9\s[].]{1,99})?'?!?$?[A-Z]{1,3}$?[0-9]{1,7}(:$?[A-Z]{1,3}$?[0-9]{1,7})?"
.Global = True
.MultiLine = True
.IgnoreCase = False
End With
Set xRetList = xRegEx.Execute(Rg.Formula)
If xRetList.Count > 0 Then
For I = 0 To xRetList.Count - 1
xRet = xRet & xRetList.Item(I) & ", "
Next
ExtractCellRefs = Left(xRet, Len(xRet) - 2)
Else
ExtractCellRefs = "No Matches"
End If
End Function
Sub GoToReferences()
Application.OnKey "+[", "ClickThroughReferences"
End Sub
Sub ClickThroughReferences()
Dim arr As Variant
arr = Split(ExtractCellRefs(ActiveCell.Value), ",")
Application.Goto Reference:=arr
End Sub'''
I think I'm only missing one small piece of code
just want to create hotkey (ctrl+[) that Goes To the cell references of a cell formula
don't think it would be more than 30 min of work for a VBA expert.
as a deliverable, I just want the code in text form, not in an excel workbook.
thanks.