Adding symbol for each cell in one column by VBA

Question

I'm trying to add "#" in each cell in the first column since Row7 until the cell is blank. And I have several files in one folder which needs to repeat. Is it possible to do this by VBA? It always shown, there's something wrong with the code. Many thanks for help.

 Sub LoopAllFilesInAFolder()

'Loop through all files in a folder
Dim fileName As Variant
fileName = Dir("C:\Users)

While fileName <> ""

    'Insert the actions to be performed on each file
    Dim last As Long
    Dim i As Long

    'Find the last row with values in Column A
    last = .Cells(.Rows.Count, 1).End(xlUp).Row
    'Loop from the 7th row to the last row.
    For i = 7 To last
        'Assign the value
        Cells(i, 1).Value = "#" & Cells(i, 1).Value
    Next i
    'This example will print the file name to the immediate window
    Debug.Print fileName

    'Set the fileName to the next file
    fileName = Dir
Wend

End Sub

user_2233308 · Answer

Please, try the next adapted code:

Sub LoopAllFilesInAFolder()
 Dim folderName As String, fileName As String, wb As Workbook
 Dim ws As Worksheet, last As Long, i As Long, arr

 folderName = "C:\Users\Lin.Yu\Desktop\NewFolder\" 'take care to the ending backslash
 fileName = Dir(folderName & "*.xlsx")

 While fileName <> ""
    Set wb = Workbooks.Open(folderName & fileName)
    Set ws = wb.Sheets(1) 'use here the appropriate sheet, if not the first one
    With ws
        'Find the last row with values in Column A
        last = .cells(.rows.count, 1).End(xlUp).row
        arr = .Range("A1:A" & last).value 'put the column in an array to make the code faster
        'Loop from the 7th array row to the last row and process.
        For i = 7 To UBound(arr)
            arr(i, 1) = "#" & arr(i, 1)
        Next i
        .Range("A1:A" & last).value = arr 'drop the processed array content
    End With
    wb.Close True 'save and close the processed workbook

    'Set the fileName to the next file
    fileName = Dir
 Wend
End Sub

Edited:

Please, test the next compact variant, using Evaluate instead of iteration:

Sub LoopAllFilesInAFolder()
 Dim folderName As String, fileName As String, wb As Workbook
 Dim ws As Worksheet, last As Long, i As Long, rng As Range

 folderName = ThisWorkbook.path & "\" ' "C:\Users\Lin.Yu\Desktop\NewFolder\"
 fileName = Dir(folderName & "*.xlsx")

 While fileName <> ""
    Set wb = Workbooks.Open(folderName & fileName)
    Set ws = wb.Sheets(1) 'use here the appropriate sheet, if not the first one
    With ws
        'Find the last row with values in Column A
         last = .cells(.rows.count, 1).End(xlUp).row
         Set rng = .Range("A7:A" & last)
         rng.value = Evaluate("""#""&" & rng.Address)
    End With
    wb.Close True 'save and close the processed workbook
    
    'Set the fileName to the next file
    fileName = Dir
 Wend
End Sub

user_4279155 · Answer

You seem to be showing two things:

The code to find all files you want to process.
The code you want to apply on the workbook in those files.

What you didn't to, is create a VBA workbook, based on the files you have found. This is explained in this URL.

user_16598419 · Answer

Tried FaneDuru's code, it worked for the first time. After that, It was either shown as above which only has # or not responding.

Adding symbol for each cell in one column by VBA

3 Answers