Is This Possible?

[Dazzawm]

I have a folder with 30 files in. Is it possible to use a code to open all files look for a word that will be in column H and then put another word in column K in one fail swoop, rather than opening each file individually? Thanks.

 

[Dazzawm]

^

 

[RSXchin]

first of all... it's "one FELL swoop".
second, what word are you looking for, and what do you want to do with it?

 

[Dazzawm]

Thanks for picking me up on my spelling, the word it needs to look for in column H is 'Cube' and the word it needs to put in column K in each file is 'Van'.

 

[RSXchin]

try this

Private Sub AllFolderFiles()
    Dim wb As Workbook
    Dim TheFile As String
    Dim MyPath As String
    MyPath = GetFolder
    ChDrive Left(MyPath, Application.WorksheetFunction.Search(":", MyPath))
    ChDir MyPath
    TheFile = Dir("*.xls")
    On Error Resume Next
    Do While TheFile <> ""
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(MyPath & "\" & TheFile)
        wb.Activate
        For i = 1 To Cells(Rows.Count, "h").End(xlUp).Row
            If Cells(i, "H") = "Cube" Then
                Cells(i, "k") = "Van"
            End If
        Next i
        wb.Close
        Application.ScreenUpdating = True
        TheFile = Dir
    Loop
    Set wb = Nothing
End Sub
Function GetFolder(Optional strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "Select a Folder"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

 

[Dazzawm]

Do I need to put this in just one file within the folder they are kept? Also can you highlight the parts I need to edit. Thanks.

 

[RSXchin]

no need to edit anything. just make sure all the worksheets (.xls) are in the same folder.

well.... change .xls to .xlsm or whatever you need to. but, that should be it.

 

[Dazzawm]

Where do I put the code? I cant seem to find it anywhere!

 

[RSXchin]

put it in a "module". It's a "container" for code.

press alt+f11 to get to the vb editor.
go to the left where you see "sheet1", "thisworkbook", etc. and right click add module
paste the code I put there

close out of that (save it of course)

click on view>macros>(whatever you called the code)

 

[Dazzawm]

Still cant see it. It wouldnt be because its a private sub ?