因为工作原因,经常需要替换各种公式,但是各种公式的名称,数量各不相同,做了一个VBA公式,供参阅~
变换前(都是男内):
总行数 | 工作表名 | 公式替换 |
61 | 男内 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
73 | 女内 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
69 | 袜子 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
63 | 文胸 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
33 | 背心 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
469 | 四季睡衣 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
145 | 秋衣秋裤 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
268 | 保暖 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
121 | 睡衣法兰绒 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
Sub 批量进行替换公式()
Dim str As String, row_count As Long
row_count = ActiveSheet.UsedRange.Rows.Count
Range("C2").Select
For i = 2 To row_count
str = Range("b" & i).Text
Range(Cells(i, 2).Address & ":" & Cells(i, 3).Address).Select
Selection.Replace What:="男内", Replacement:=str
Next
End Sub
变换前(都是男内):
总行数 | 工作表名 | 公式替换 |
61 | 男内 | NOT(ISNA(VLOOKUP(C2,男内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,男内!$A$1:$AB$3000,28,0), |
73 | 女内 | NOT(ISNA(VLOOKUP(C2,女内!$A$1:$AB$3000,28,0))),VLOOKUP(C2,女内!$A$1:$AB$3000,28,0), |
69 | 袜子 | NOT(ISNA(VLOOKUP(C2,袜子!$A$1:$AB$3000,28,0))),VLOOKUP(C2,袜子!$A$1:$AB$3000,28,0), |
63 | 文胸 | NOT(ISNA(VLOOKUP(C2,文胸!$A$1:$AB$3000,28,0))),VLOOKUP(C2,文胸!$A$1:$AB$3000,28,0), |
33 | 背心 | NOT(ISNA(VLOOKUP(C2,背心!$A$1:$AB$3000,28,0))),VLOOKUP(C2,背心!$A$1:$AB$3000,28,0), |
469 | 四季睡衣 | NOT(ISNA(VLOOKUP(C2,四季睡衣!$A$1:$AB$3000,28,0))),VLOOKUP(C2,四季睡衣!$A$1:$AB$3000,28,0), |
145 | 秋衣秋裤 | NOT(ISNA(VLOOKUP(C2,秋衣秋裤!$A$1:$AB$3000,28,0))),VLOOKUP(C2,秋衣秋裤!$A$1:$AB$3000,28,0), |
268 | 保暖 | NOT(ISNA(VLOOKUP(C2,保暖!$A$1:$AB$3000,28,0))),VLOOKUP(C2,保暖!$A$1:$AB$3000,28,0), |
121 | 睡衣法兰绒 | NOT(ISNA(VLOOKUP(C2,睡衣法兰绒!$A$1:$AB$3000,28,0))),VLOOKUP(C2,睡衣法兰绒!$A$1:$AB$3000,28,0), |