Turning a customer number in a cell to an external hyperlink

[Excel_Test_21]

each morning i receive a report that has a list of customer job numbers and i am looking for a way to create a macro to have each number converted to a hyperlink that takes me to our company's customer record.

right now i have to copy the number from excel and past it in our company's look up screen to return the results.

the job # will always be in the same column and it comes up in our browser as the location ID in the script below.

for example

13582232​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=13582232&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1
13582233​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582234​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582235​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=
13582236​	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=

 

[Kerryx]

or else use formula

Book1
	A	B	C
1	13582232	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=13582232&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1	13582232
2	13582233	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582233
3	13582234	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582234
4	13582235	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582235
5	13582236	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582236
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=HYPERLINK("https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID="&A1&"&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1",A1)

 

[Excel_Test_21]

or else use formula

Book1
	A	B	C
1	13582232	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=13582232&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1	13582232
2	13582233	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582233
3	13582234	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582234
4	13582235	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582235
5	13582236	https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=	13582236
Sheet1

Cell Formulas
Range	Formula
C1:C5	C1	=HYPERLINK("https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID="&A1&"&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1",A1)

 

[Excel_Test_21]

Thank you, i like how the code automatically changes it blue when using the =HYPERLINK
how can i get it not to be in the C column and automatically replace the number in column A
or do i have to do this in another sheet and have it pasted in to my master log sheet(the workable one)



what is the or else formula...will that script automatically change the whole column to hyper link? i see it has several lines.

 

[Kerryx]

Sorry unsure what you mean this is an excel formula not VBA code, the formula can be put in any location you want i just put it in column C to show how it works.You just put the formula in column B to get

Book1
	A	B
1	13582232	13582232
2	13582233	13582233
3	13582234	13582234
4	13582235	13582235
5	13582236	13582236
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=HYPERLINK("https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID="&A1&"&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1",A1)

If you just want to run some VBA to convert the numbers in column A without adding any extra columns into your hyperlink you could try

Sub Hyperlink()
   For Each xCell In Range("A:A")
      If xCell.Value <> "" Then
         ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=" & Replace(xCell.Formula, "www.", "") & "&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1", TextToDisplay:="Website"
      End If
   Next xCell
End Sub

 

[Excel_Test_21]

Sorry unsure what you mean this is an excel formula not VBA code, the formula can be put in any location you want i just put it in column C to show how it works.You just put the formula in column B to get

Book1
	A	B
1	13582232	13582232
2	13582233	13582233
3	13582234	13582234
4	13582235	13582235
5	13582236	13582236
Sheet1

Cell Formulas
Range	Formula
B1:B5	B1	=HYPERLINK("https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID="&A1&"&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1",A1)

If you just want to run some VBA to convert the numbers in column A without adding any extra columns into your hyperlink you could try

Sub Hyperlink()
   For Each xCell In Range("A:A")
      If xCell.Value <> "" Then
         ActiveSheet.Hyperlinks.Add Anchor:=xCell, Address:="https://SampleCompanyName/Office/Location/LocationInformation.aspx?locationID=" & Replace(xCell.Formula, "www.", "") & "&_FK=&inCurrentCompanyStructureScope=1&hasSecurityRightToModify=1", TextToDisplay:="Website"
      End If
   Next xCell
End Sub

 

[Excel_Test_21]

2023_04_05_hyperlink_code.xlsm
	A	B
1	Service Order ID	Customer ID
2	14062871	14062874
3	14062872	14062875
4	14062873	14062876
Sheet4

 

[Excel_Test_21]

how can i send you the sheet to review the code, i am not getting it to work.

if i can get it to work for column A i want to try and create a different link for column B

 

[Kerryx]

Just use something like Dropbox, Mega, or any one that allows you to share a file and share the link here.
I assume when you entered the VBA you saved the file with the .xlsm extension.
Click below to download file I used
Hyperlink example

 

[Excel_Test_21]

Just use something like Dropbox, Mega, or any one that allows you to share a file and share the link here.
I assume when you entered the VBA you saved the file with the .xlsm extension.
Click below to download file I used
Hyperlink example

how can i write a code to have column b go to a different web page? i tried to keep the one for A2 and just copy the code changing the letter A to B and it did not work.

BTW i really appreciate you taking the time to help me learn this.