VBA find offset & replace cell value

[Bagsy]

Can anybody help me a bit here, I am trying cycle through a folder open each Workbook in turn find a value in column I on Cost sheet and then offset from that cell to column L , I’e 3 columns across. Then I need to change that cell value to 60.
The code to cycle through the files and open them is OK. What I am having trouble with is the find and then offset, between the commented out section
Any help is appreciated

Sub MakeChanges()
   Dim i As Long
   Dim ws As Worksheet
   Dim Wbk As Workbook
   Dim Ans As Variant

  With Application
  .DisplayAlerts = False
  .ScreenUpdating = False
End With

Ans = MsgBox("Have you created a back-up.", vbYesNo)
If Ans = vbNo Then Exit Sub

Set ws = Worksheets("Sheet1")
'myDir = "W:\Sub-Contract\Test"
myDir = "W:\Sub-Contract\Test\Cost Sheets"

myFile = Dir(myDir & Application.PathSeparator & "*.xlsm", vbDirectory)
ws.Range("B3:D500").ClearContents 'Clear Data in Column B3 to D500
ws.Range("B3:D500").Interior.Color = xlNone 'Clear formatting in Column B3 to D500

i = 3 ' this is staring a Row 1 then offsetting to row 3
   Do While myFile <> ""
      ws.Cells(i, 2) = myFile ' this is offsetting from column A to column B to enter the file name
    Set Wbk = Workbooks.Open(myDir & "\" & myFile, True) ' open the file

On Error GoTo Handler:
'xxxxxxxxxxxxxxxxxxxxxxxxxxx
'Enter code here

Sheets("Cost Sheet").Select
ActiveSheet.Unprotect
Columns("I:I").Select


fnd = "Normal Time (NT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Overtime (OT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Double Time (DT))"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Night Shift(NS)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"

ActiveSheet.Protect


'xxxxxxxxxxxxxxxxxxxxxxxxxxxx

Wbk.Close True

GoTo LastLine ' missing the error handler out if there is no errors

Handler:
    ws.Cells(i, 2).Interior.ColorIndex = 22
    Wbk.Close True
    GoTo LastLine
    
LastLine:
    myFile = Dir
        i = i + 1
    Loop

With Application
  .DisplayAlerts = True
  .ScreenUpdating = True
End With
 
End Sub


Private Function SheetExists(sname) As Boolean

SheetExists = False
For Each ws In Worksheets
If UCase(ws.Name) = UCase(sname) Then SheetExists = True
Next
End Function

 

[Bagsy]

Bump
Does anybody know if it is actually possible to use excel find function in VBA offset from the found cell. I can't seem to find it mentioned anywhere.
All help is very much appreciated

 

[Akuini]

fnd = "Normal Time (NT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Overtime (OT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Double Time (DT))"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Night Shift(NS)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"

So you want to find those 4 string in col I? then if it's found then change the formula in col L to "60"?
For each string, do you expect to find only one match or multiple match?

 

[Bagsy]

Hi Akuini
Thanks for offering your help
Yes that is exactly what I am trying to achieve there will only ever be one occurrence of each, I then need to offset 3 columns across. And change that cell value to 60
Basically I need to change all the labour rates to £60.00

 

[Akuini]

Ok, try replacing this part:

fnd = "Normal Time (NT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Overtime (OT)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Double Time (DT))"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"
fnd = "Night Shift(NS)"
    ActiveCell.Offset(0, 3).FormulaR1C1 = "60"

with this:

Dim x, ary, res
ary = Array("Double Time (DT))", "Night Shift(NS)", "Normal Time (NT)", "Overtime (OT)")
For Each x In ary
    res = Application.Match(x, Range("I:I"), 0)
     If IsNumeric(res) Then
        Range("L" & res).FormulaR1C1 = "60"
     End If
Next

 

[Bagsy]

Hi Akuini
Thanks for helping, absolutely brilliant.
All the best Gary

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.