我有一个用户表单,您可以在其中编辑 Excel 工作表单元格中的文本。由于输入框没有跳过按钮,我创建了一个用户窗体,您可以在整个工作簿中搜索一个值并将其替换为另一个值条目。问题是我想逐个编辑找到的值,而不是同时编辑所有值。我有以下代码,但它的作用是,它替换每张纸中找到的值的每个第一次出现。我如何更改它替换第一张表中的值然后替换第二张表中的值的逻辑,依此类推。
Dim ws As Worksheet
Dim Loc As Range
Dim StrVal As String
Dim StrRep As String
Private Sub CommandButton1_Click()
StrVal = UserForm1.TextBox3.Text
If Trim(StrVal) = "" Then Exit Sub
For Each ws In ThisWorkbook.Worksheets
With ws.UsedRange
Set Loc = .Cells.Find(What:=StrVal)
If Not Loc Is Nothing Then
'Do Until Loc Is Nothing
Application.Goto Loc, False
StrRep = TextBox1.Text
If Not StrRep = "" Then
Loc.Value = StrRep
Set Loc = .FindNext(Loc)
Else
Exit Sub
End If
'Loop
End If
End With
Set Loc = Nothing
Next
End Sub
我创建了这个,它可以工作,但现在有一个问题,即使没有现有值,它也会跳过。
Set ws = ThisWorkbook.ActiveSheet
Set Loc = ws.Cells.Find(what:=StrVal)
If Not Loc Is Nothing Then
Application.Goto Loc, False
StrRep = TextBox1.Text
If Not StrRep = "" Then Loc.Value = StrRep
Else
Worksheets(ActiveSheet.Index + 1).Select
End If
Try this
For Each ws In ThisWorkbook.Worksheets
Application.Goto ws.Range("A1"), True
Set Loc = ws.Cells.Find(What:=strVal, After:=ws.Cells(Rows.Count, Columns.Count), SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) 'You can change MatchCase to True for better checking
If Not Loc Is Nothing Then
strFirstAddress = Loc.Address
changeBool = False
Do
strRep = Me.TextBox1.Text
If Not strRep = "" Then
Application.Goto ws.Range(Loc.Address), False
If MsgBox("Do you want to change the value of cell " & Loc.Address(0, 0) & " of worksheet " & ws.Name & "?", vbYesNo + vbQuestion, "Question") = vbYes Then
Loc.Value = strRep
If Loc.Address = strFirstAddress Then
changeBool = True
Else
changeBool = False
End If
Else
changeBool = False
End If
End If
Set Loc = ws.Cells.FindNext(After:=Loc)
If Not Loc Is Nothing Then
If Loc.Address = strFirstAddress Then Set Loc = Nothing
End If
If Not Loc Is Nothing And changeBool = True Then strFirstAddress = Loc.Address
Loop While Not Loc Is Nothing
End If
Next ws
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)