Not understanding loop in vba

[NickvdB]

Hi all,

Trying to understand a loop, but not getting there, I would like to have a vba with a list of files:
File1.xlsm
File2.xlsm
File20.xlsm

And then have these steps repeated

Range("U46:AV46").Select
Selection.Replace What:="$PG$", Replacement:="$QK$", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Selection.Replace What:="$45", Replacement:="$46", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Anybody able to help?

 

[Jeffrey Mahoney]

Are you asking for VBA code to open each of those files and replace two things in Range("U46:AV46")? I'm guessing that there is more than one sheet in each of those workbooks, so what sheet name is that supposed to change?

Jeff

 

[NickvdB]

Thanks for reply, already found a working solution. Sorry to bother

 

[Jeffrey Mahoney]

Thank you for letting us know

 

[jolivanes]

For people that have the same or similar problem and that search the web for a solution, it would be helpful if you show your solution.
Thank you.

 

[Jeffrey Mahoney]

@jolivanes
"I fear the day that technology will surpass our human interaction. The world will have a generation of idiots."

I think we are close to that. There are only a few individuals in Tech companies that know the secret to making computer processors. It's not going to take much to tip the bucket. It won't leave idiots; our society will regress to a pre-industrial, agrarian based economy again.

 

[NickvdB]

For people that have the same or similar problem and that search the web for a solution, it would be helpful if you show your solution.
Thank you.

Not sure if I agree to that thinking, because a) I did not come up with a solution only found it on internet (copyright) and b) most likely there are a lot of improvements to be found on this code, but for me it is working. Anyhow, this is the macro as I am using it now, so not written by me, and multiple file names are in A1:A60:

Sub Update()
    Dim lr As Long
    Dim i As Integer
    Dim WBSsource As Workbook
    Dim FileNames As Variant
    Dim msg As String
    With ThisWorkbook.Sheets("Macro")
        lr = .Cells(.Rows.Count, "A").End(xlUp).Row
        FileNames = .Range("a1:a" & lr).Value
    End With
    For i = LBound(FileNames, 1) To UBound(FileNames, 1)
        On Error Resume Next
        If FileNames(i, 1) Like "*.xlsm" Then
            Set WBSsource = Workbooks.Open(FileNames(i, 1), _
                                           ReadOnly:=False, _
                                           Password:="")
            If Err = 0 Then
                With WBSsource
           'To be repeated steps paste here
                            .Close True
                End With
            Else
                msg = msg & FileNames(i, 1) & Chr(10)
                On Error GoTo 0
            End If
        End If
        Set WBSsource = Nothing
    Next i
    If Len(msg) > 0 Then
        MsgBox "The Following Files Could Not Be Opened" & _
               Chr(10) & msg, 48, "Error"
    End If
End Sub

 

[jolivanes]

@NickvbB
Thank you very much. If it helps people, that is all that counts. I am sure you know how frustrating it can be if you think you found a solution when you see a header that gives the impression of having a solution for your problem only to find that it does not.

@Jeffrey Mahoney
You are probably aware that quite often it is mentioned that Albert Einstein said this but according to Snopes and others, it is not.
However, I still like the saying and for me it certainly has a ring of the truth. as you alluded to I think.
The last two words of your signature remind me of always telling the truth, even if people don't want to hear it. You hope that they realize it is an opinion and gives rise to a discussion.