Macro to find missing extensions

[g2only]

Hi, I'm a newbie...just to state that up front, please don't throw rocks!

I'm a creative designer by trade, but have been getting into more 'expanded' requirements by clients.

Anyways, here's my question, I have an ongoing monthly gig that I'm supplied with a spreadsheet that includes multiple columns with field entries of file names (some_file.pdf). The problem is sometimes there are field entry missing the extension (.pdf) and up till now I've been having to find them manually. I did a search and haven't found anybody that's already made a macro to do so (just to save the "...did you search the forums, yet, dumb-dumb?" comments), so I was hoping an experienced macro-maker could lend me a hand.

Much appreciated in advanced!

George

 

[NeonRedSharpie]

Sub findExtension()

    For Each cell In ActiveSheet.UsedRange
        If UCase(Right(cell, 4)) <> ".PDF" Then cell = cell & ".pdf"
    Next cell
End Sub

Try this. I used the usedRange because I don't know your columns...so it might attack other cells with the .pdf extension. Be sure to try this on a saved copy of your workbook so as not to endanger data.

 

[g2only]

Thanks, NeonRedSharpie!

If I were to specify the columns, how would this code change?

(Columns D-I)

 

[NeonRedSharpie]

Thanks, NeonRedSharpie!

If I were to specify the columns, how would this code change?

(Columns D-I)

Give this a go.

Sub findExtension()


    Dim startRow As Integer
    Dim maxRow As Long
    Dim startCol As Integer
    Dim endCol As Integer
    
    startCol = 4 '4 = D
    maxCol = 9 '9 = I
    startRow = 2 'THIS ASSUMES HEADERS. CHANGE TO 1 IF NO HEADERS
    maxRow = 0


    For x = startCol To maxCol
        If Cells(Rows.Count, x).End(xlUp).Row > maxRow Then
            maxRow = Cells(Rows.Count, x).End(xlUp).Row
        End If
    Next x


    For Each cell In Range(Cells(startRow, startCol), Cells(maxRow, maxCol))
        If cell.Value <> "" And UCase(Right(cell, 4)) <> ".PDF" Then cell = cell & ".pdf"
    Next cell
End Sub