宏excel替换单元格

Question

我从来没有做过宏，我必须制作一个vb宏。如果找到了"B"单元格的值，我的宏必须循环一张表的所有行，然后在另一张表中查找，如果找到，我必须用侧列中匹配的值替换该值。

因为我从来没有这样做过，我不知道该怎么开始。有人已经做了类似的事情吗？

Answer 1

这是开始的地方。

这应该足以让你朝着正确的方向前进。

Answer 2

我做了一个看起来有效的宏，但它没有取代链接的值

Sub ReplaceLink()
Dim searchedString As String, replaceString As String
Dim hLink As Hyperlink
'loop shhet
For Each sh In ActiveWorkbook.Worksheets
    For Each hLink In sh.Hyperlinks
        'searched address
        searchedString = Replace(hLink.Address, " ", "%20")
        'new url
        replaceString = NewUrl(searchedString)
        'Search for the specified text
        If Not IsEmpty(replaceString) Then
            hLink.Address = Replace(hLink.Address, hLink.Address, replaceString)
        Else
            hLink.Range.Interior.ColorIndex = 3
        End If
    Next hLink
Next sh

结束子

函数NewUrl（searchedString作为字符串）作为字符串Dim MyPath$，MyWB$Dim GCell As Range

MyPath = "C:temp"
'The name of the workbook in which to search.
MyWB = "migration_link.xls"
Workbooks.Open Filename:=MyPath & MyWB
'Set GCell = ActiveSheet.Cells.Find(searchedString)
Set GCell = ActiveSheet.Cells.Find(searchedString)
'if a matching is found, set new url
If Not GCell Is Nothing Then
    NewUrl = ActiveSheet.Range("C" & GCell.Row)
Else
    NewUrl = ""
End If

结束函数