Apply text to specific cell

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,825
Office Version
  1. 2007
Platform
  1. Windows
I have the working code in use shown below.

Currently the working code applies a hyperlink to the customers name in column B
It is at the same time i wish to add a text value for the same row in question to column C & the text is DISCO II KEY

On my form users type anything to be placed in the cell, so by making this small edit should a user type landrover the code will overwrite it to DISCO II KEY
This then helps when i look for DISCO II KEY in another form where at present iwill have missed this customer in question because the user wrote landrover

Thanks


VBA Code:
Sub DISCOHYPERLINK()
      
    Const sPath As String = "C:\Users\Ian\Desktop\REMOTES ETC\DISCO II CODE\DISCO II PDF\"
  Dim sFile As String, i As Long
 
  Dim lastrow As Long
  With Sheets("POSTAGE")
    lastrow = .Range("B" & .Rows.Count).End(xlUp).Row
    For i = lastrow - 10 To lastrow
    
      sFile = sPath & .Range("B" & i).Value & ".pdf"
      If Len(Dir(sFile)) Then
        .Range("B" & i).Hyperlinks.Add Anchor:=.Range("B" & i), Address:=sFile
      End If
    Next
  End With
  MsgBox "CUSTOMER WAS HYPERLINKED.", vbInformation, "DISCO II HYPERLINK MESSAGE"
 
  'NOW SAVE & CLOSE DISCO CALC WORKBOOK
  Workbooks("DISCO CALC.xlsm").Close SaveChanges:=True
End Sub
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Change this part:

VBA Code:
      If Len(Dir(sFile)) Then
        .Range("B" & i).Hyperlinks.Add Anchor:=.Range("B" & i), Address:=sFile
      End If

to this:

VBA Code:
      If Len(Dir(sFile)) Then
        .Range("B" & i).Hyperlinks.Add Anchor:=.Range("B" & i), Address:=sFile
        .Range("C" & i).Value = "DISCO II KEY"
      End If
 
Upvote 0
Solution

Forum statistics

Threads
1,224,543
Messages
6,179,429
Members
452,914
Latest member
echoix

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top