Replace with formula after find?

[ExcelJunior]

I would like to know if it is possible to complete a "Find and Replace" but replace with a formula as opposed to a value?

 

[ExcelJunior]

I have been placed back on completing this task. I am getting a better understanding of the functions in your VB script. If I would like it to find any instances of a 5 digit part number anywhere in the sheet and complete the hyperlinking, what would need to be changed in the macro?

 

[ExcelJunior]

I am receiving an error related to range while running the following macro:

Sub Macro1()
Dim myRng As Range
Dim c As Range
Dim cCount As Long
Dim i As Long
cCount = ActiveSheet.UsedRange.Rows.Count
Set myRng = ActiveSheet.Range("H1:I" & cCount)
    For Each c In myRng
    If c.Value <> "" Then
        c.Hyperlinks.Add anchor:=c, Address:="http://shop.domain.com/part/" & c.Value, ScreenTip:=c.Value
    End If
    Next c
End Sub

The part numbers that I would like to hyperlink are in columns H, I, L, and M. I would like to know how to better control the ActiveSheet.Range to only alter values found in specific columns. Is there a way to define this not as a range?

 

[dave3009]

Hi

Seems you cannot use .value if the cell is numeric, so just change this to .text instead. Also to loop through non-contagious ranges you can create an array and loop through that.

Sub Macro4()
Dim myRng As Range
Dim c As Range
Dim cCount As Long
Dim myArr As Variant
myArr = Array("H", "I", "L", "M")
Dim i As Long
cCount = ActiveSheet.UsedRange.Rows.Count + 1
For Each arr In myArr
Set myRng = ActiveSheet.Range(arr & "1:" & arr & cCount)
    For Each c In myRng
    If c.Value <> "" Then
        c.Hyperlinks.Add Cells(c.Row, c.Column), "http://www.domain.com/part/" & c.Text, c.Text
    End If
    Next c
Next arr
End Sub

 

[ExcelJunior]

Excellent! Thank you again Dave! This has worked great for all of the spreadsheets I have done so far, wish me luck on the remainder!

- Excel Jr.