File renaming using cell values

[English Puma]

Hello All.

I'm trying to write a section of an excel macro which does the folowing:

Starting with cell M2 selected and avtive:

As long as the in column J has a value in (activecell.offset(0,-3)<> vbnullstring), search for a file in a specified folder with the filename in column J.

If the file exists, move the active cell down 1 row and begin the process again

If the file search fails, search for a file which ends with the string in column L (which always has the form ".12345"). Rename that file (there will be one and only ever one), rename it to the value in column J then move the active cell down one row and begin the process again.

I think I can get the Do While set up right but, either can't phrase the file search for "*"&"L2" or the rename file to J bit. Any suggestions???

Thanks

Puma

 

[English Puma]

This is where I am at the moment (but, it won't compile as it's complaining about a loop without a do... which i'm sure is there!). I feel soooooo close!!!!

Dim fs, i
Dim A As String

Columns("M:M").Select
Selection.Insert Shift:=xlToRight
Range("M2").Select

Range("M2").Activate

Set fs = Application.FileSearch

Do While ActiveCell.Offset(0, -3) <> vbNullString
A = ActiveCell.Offset(0, -3).Value
Set fs = Application.FileSearch
With fs
.LookIn = "C:\TEMP\"
.Filename = A
If .Execute Then
For i = 1 To .FoundFiles.Count
ActiveCell.Offset(1, 0).Activate
Next
Else
Set fs = Application.FileSearch
With fs
.LookIn = "C:\TEMP\"
.Filename = "*" & ActiveCell.Offset(0, -1).Value
If .Execute Then
For i = 1 To .FoundFiles.Count
Name Application.FileSearch.Filename As "*" & ActiveCell.Offset(0, -1).Value
ActiveCell.Offset(1, 0).Activate
Next
End If
End With
Loop