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?

 

[dave3009]

In a standard VBA module place this code,

Sub Macro1()
Dim partNumber As Variant
Dim found As Range
Dim cCount As Long
Dim i As Long
partNumber = InputBox("Enter Part Number")
cCount = Range("A" & Rows.Count).End(xlUp).Row
Set found = Range("A1")
MsgBox cCount
    For i = 1 To cCount
    MsgBox i
        Set found = Range("A:A").Find(what:=partNumber, after:=found, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
        If Not found Is Nothing Then
            found.Hyperlinks.Add anchor:=found, Address:="http://shop.domain.com/part/" & partNumber, ScreenTip:=partNumber
        End If
    Next i
    
End Sub

It will prompt you for a part number then convert any instances of that part number to a hyperlink. We can tweak it a little to better suit your needs.

 

[ExcelJunior]

THANK YOU DAVE!!

Finds the partnumber and causes the value to become a linked URL, I am experiencing an odd count up after entering the partnumber, beginning with 336 (number of rows in the sheet), then 1,2,3,4,5,6,.....until 336 is reached again, at which point it takes me back to the VBA module and I can run it again entering a different value.

I am not well versed in VBA so troubleshooting this is somewhat of a challenge Any hints on how to eliminate this issue?

Thank you again Dave! Check your PM.

 

[dave3009]

So do you just want it to run down the column and whatever value it finds it'll be converted to a hyperlink for http://shop.com/parts/x and so on?

 

[ExcelJunior]

There are 4 columns which contain part numbers so I suppose it would be most efficient to have it look for any instances contained within those 4 columns.

I can however run it on a per column basis. I have a feeling that having this capability will allow us to perform this on other spreadsheets that would benefit from hyperlinking as well.

Thank you again Dave.

 

[dave3009]

Try this instead

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

ActiveSheet can be made more specific if needed.

 

[ExcelJunior]

So leaving it to one column (for simplicity's sake) I would need to adjust ActiveSheet.Range to begin where the part numbers begin A3 to where the part numbers end A336. I have tried a few variations, which appear to run but are not leaving the part numbers with a hyperlink as they were before.

Need to brush up on my VBA

 

[dave3009]

Actually you can take the partNumber bit out all together, this will cycle through ever cell in the activesheet in columns A:D and convert the value to a hyperlink.

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("A1:D" & 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

 

[ExcelJunior]

Dave - I encounter "Run-time error '5': Invalid procedure call or argument" when running the lastest bit of VBA.

To help me gain knowledge of VBA could you correct my understanding/misunderstandings of the functions of the VBA below?

Sub Macro1()
Dim partNumber As Variant - [COLOR="Red"]defines partnumber as the value entered when prompted by the code below[/COLOR]
Dim myRng As Range - [COLOR="red"]defines the variable used for the range[/COLOR]
Dim c As Range - [COLOR="red"]defines variable to be used for each instance?[/COLOR]
Dim cCount As Long - [COLOR="red"]counting each instance?[/COLOR]
Dim i As Long - [COLOR="red"]no idea :)[/COLOR]
partNumber = InputBox("Enter Part Number") -[COLOR="red"] creates call for variable defined by value entered in the inputbox[/COLOR]
cCount = ActiveSheet.UsedRange.Rows.Count - [COLOR="red"]defines the method of counting and limits it to only the active sheet [/COLOR]
Set myRng = ActiveSheet.Range("A1:D" & cCount) - [COLOR="red"]defines the range using only a letter for an individual colum or letter:anotherletter for multiple colums[/COLOR]
       For Each c In myRng - [COLOR="red"]tells the process to continue while there is a count in the defined range[/COLOR]
    If c.Value <> "" Then
        c.Hyperlinks.Add anchor:=c, Address:="http://shop.domain.com/part/" & partNumber, ScreenTip:=partNumber - [COLOR="red"]creates the hyperlink[/COLOR]
    End If 
    Next c - [COLOR="red"]continues until the previous statement is met[/COLOR]
End Sub - [COLOR="red"]ends the logic statement when the last count has been met[/COLOR]

 

[dave3009]

Sub Macro1()
Dim myRng As Range Set the range we want to apply the hyperlinks to
Dim c As Range  we need a single cell within the above
Dim cCount As Long  last used row in the dataset
Dim i As Long  For the for each loop
cCount = ActiveSheet.UsedRange.Rows.Count  Sets the actual last row
Set myRng = ActiveSheet.Range("A1:D" & cCount)  Sets the four columns we're interested in
    For Each c In myRng  Loop through the cells in that range
    If c.Value <> "" Then
        c.Hyperlinks.Add anchor:=c, Address:="http://shop.domain.com/part/" & c.Value, ScreenTip:=c.Value  Sets up the hyperlink
    End If
    Next c Onto the next until we're all done
End Sub

Hope this helps

Regards


Dave

 

[ExcelJunior]

We are very close and I know that I just need to gain a better understanding of VBA and its syntax, but alas I have been placed on another task today to return to this another.

Thank you for your help Dave!