im trying to create a VBA code to search for a a persons code in column "B" and return the offset of the column in column "D" and replace the value of the cell with a new input i managed to do so but i need to do it across all selected sheets i dont know what im doing wrong because when i select several sheets it just executes the code on the first sheet and ignores the rest.
https://drive.google.com/file/d/1Kdol7qnfgstSc0mWV5hN9X0O1PcVwsjx/view?usp=sharing
Sub RunCode()
Dim rg As Range, c As Range
Dim str As String
Dim A As Variant
Dim ws As Worksheet
Set rg = ActiveSheet.Columns("B")
str = "PEC-00" & Application.InputBox(Prompt:="ID: ")
A = Application.InputBox(Prompt:="New Value: ")
With rg
Set c = .Find(str, , xlValues)
Application.ScreenUpdating = False
For Each ws In ActiveWindow.SelectedSheets
ws.Select
c.Offset(, 2) = A
Next ws
Application.ScreenUpdating = True
End With
End Sub
i tried to loop the code to all selcted sheets but the codes still executes on only the first sheet selected
CodePudding user response:
Sub RunCode()
Dim rg As Range, c As Range
Dim str As String
Dim A As Variant
Dim ws As Worksheet
Application.ScreenUpdating = False
str = "PEC-00" & Application.InputBox(Prompt:="ID: ")
A = Application.InputBox(Prompt:="New Value: ")
For Each ws In ActiveWindow.SelectedSheets
Set rg = ws.Columns("B")
Set c = rg.Find(str, , xlValues)
c.Offset(, 2) = A
Next ws
Application.ScreenUpdating = True
End Sub
Note: this is untested