在excel上如何搜索&使用库替换列上的字符串 [英] On excel how to search & replace a string on a column using a library

问题描述

我有一个大约10,000行和5,000个唯一ID的大型excel电子表格.我想做一个基于数据库的搜索和替换.

I have this large excel spreadsheet around 10,000 lines and 5,000 unique ids. That I want to like do a search and replace that is based on a data library.

从:

收件人:

我知道这可以通过使用快速搜索并在excel上进行替换来完成，但是如果数据具有大约5,000个唯一的agent-id，这将是一项艰巨的任务.

I know this can be done using a fast search and replace on excel, but if the data has around 5,000 unique agent-ids, it can be a daunting task.

有人建议吗?

提前谢谢！

推荐答案

我能够制作一个vb-宏代码来完全满足我的需要.我可能需要调整一些特定的单元格，但现在可以使用.

I was able to make a vb-macro code to do exactly what I need. I may need to adjust some specific cells, but it works for now.

Sub FindAndReplace()
' FindAndReplace Macro
' @author Louie Miranda
' Ability to find the range of ids against another worksheet
' and insert the name on the main sheet
'
    ' Loop over the current worksheet
    For Each c In Worksheets("RECORDS").Range("A3:A7").Cells

        ' Go to Agents sheet
        Sheets("AGENTS").Select

        ' Do a search
        Cells.Find(What:=c, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

        Application.CutCopyMode = False

        ' Choose beside the column to copy
        ActiveCell.Offset(rowOffSet:=0, columnOffset:=-1).Activate
        Selection.Copy

        ' Back to records sheet
        Sheets("RECORDS").Select

        ' Paste on the current row, plus arrange on which row/offset
        Range(c.Address).Offset(0, 1).Select
        ActiveSheet.Paste

    Next c
End Sub

这篇关于在excel上如何搜索&使用库替换列上的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！