UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Autolink, Office 2010    
 
   
pwbrownapcom
post May 24 2019, 08:31 AM
Post#1



Posts: 9
Joined: 5-March 19



This may or may not be possible but thought I would pass this and keep my fingers crossed.

There is a cell in a massive sheet where users can type in an account number and a vlookup box will appear next to it telling them what row to scroll down to so they can see that account. (there are over 1,000 accounts). What I was hoping to do was perhaps set it up so when they type the account number they want to see, either it would automatically take them to the proper row or the msg would appear as a hyper link to correct row and not just a msg. Do not know how to make a "flexible hyperlink" or if this is even possible.

But thank you in advance for any assistance.
Go to the top of the page
 
Zaddicus
post May 24 2019, 08:54 AM
Post#2



Posts: 122
Joined: 3-April 19
From: Cardiff


Hi there,

This is something that can be done through VB easily. Let's say they enter the Account number into A1, and the Vlookup is therefore in cell B1.

Theoretically the following should work

CODE
Dim r As Integer

r = Range("B1").Value
Cells(r, 1).Select


let me know if there's any issues

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
pwbrownapcom
post May 24 2019, 09:28 AM
Post#3



Posts: 9
Joined: 5-March 19



This is great news! Only thing is that where to add to VB? I right clicked on the tab and went to "View Code", then used the code provided:

Dim r As Integer

r = Range("E4").Value
Cells(r, 1).Select


B4 = Account number
E4 = "Go To Row 449" (based on the vlookup of the account number entered in B4.


Sure I am simply not putting the code in the right area or using correctly?

Go to the top of the page
 
Zaddicus
post May 24 2019, 09:51 AM
Post#4



Posts: 122
Joined: 3-April 19
From: Cardiff


Okay so we will need to add some other code in order to fully complete this.

Create a new module for the 'jump to' code (Copy in the below)
CODE
Sub JumpToAcc()

Dim r As Integer

r = Range("B1").Value
Cells(r, 1).Select

End Sub

Then on the active worksheet where you enter the account number you add the following code:
CODE
Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Call JumpToAcc
    End If
End Sub


Note - you will need to change the cell 'B1' in the first sub, and '$B$1' in the second.

HTH smile.gif

EDIT:
Just noticed if the VLookup is a string and not just a number then you will need to pull the numeric value on it's own into another cell and reference that instead.
So for Example in 'F4' use this formula to extract the numeric value for the row number
CODE
=VALUE(RIGHT(E4,LEN(E4) - MIN(SEARCH({0,1,2,3,4,5,6,7,8,9}, E4&"0123456789")) +1))

This post has been edited by Zaddicus: May 24 2019, 09:56 AM

--------------------
~ Castiel


Skype: live:castielswift
Go to the top of the page
 
pwbrownapcom
post Jun 18 2019, 01:08 PM
Post#5



Posts: 9
Joined: 5-March 19



working great and thank you to all!!!! just hope they don't expect this type of automation on all the files.. smile.gif
Go to the top of the page
 
Debaser
post Jun 20 2019, 03:44 AM
Post#6



Posts: 122
Joined: 11-October 18



FWIW, you could also do this with a Hyperlink formula. Say you are matching the value in B4 to column A, then:

=HYPERLINK("#A"&MATCH(B4,$A:$A,0),"Go to row")

would be a dynamic hyperlink to the relevant cell in column A.
This post has been edited by Debaser: Jun 20 2019, 03:45 AM
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    24th June 2019 - 04:09 AM