Change the value of the first blank cell in a column (VBA)

[Joe9238]

Hi all,

I have a code that gathers data from files and puts them in a list. When it cannot find a file, I want it to use the 'else' command to add a message saying 'File Not Found'. I was thinking to do this by selecting the first blank cell in the column and changing its value.

I think it would look something like:
Range("first blank cell").value = "File Not Found"

but I'm not sure in the slightest.
All advice and help welcome.
Thanks in advance

 

[Joe4]

It would probably be helpful if you could post the code that you have.

 

[Joe9238]

Here it is:

 Option Explicit
Sub GatherData()
Range("E1").Value = "Quoted By"
Range("F1").Value = "Client Name"
Range("G1").Value = "Email"


Dim sFolder As String




    Application.ScreenUpdating = True
    
    With Application.FileDialog(msoFileDialogFolderPicker)
        .InitialFileName = Application.DefaultFilePath & "\"
        .Title = "Please select a folder..."
        .Show
        If .SelectedItems.Count > 0 Then
            sFolder = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With
    
    Call Consolidate(sFolder, ThisWorkbook)
End Sub
Private Sub Consolidate(sFolder As String, wbMaster As Workbook)
    Dim wbTarget As Workbook
    Dim ary(3) As Variant
    Dim lRow As Long
    Dim objFile As Object
    Dim objFso As Object
    Dim objFiles As Object
        Dim objSubFolders As Object
        Dim objSubFolder As Object
    
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objFiles = objFso.GetFolder(sFolder).Files


Dim CodeNames As Variant, i As Long
CodeNames = Range("A2:A" & Cells(Rows.Count, "A").End(xlUp).Row)


For Each objFile In objFiles
For i = 1 To UBound(CodeNames, 1)
If objFile.Name Like "*" & CodeNames(i, 1) & "*" Then


    'Create objects to enumerate files and folders
    Set objFso = CreateObject("Scripting.FileSystemObject")
    Set objSubFolders = objFso.GetFolder(sFolder).subFolders
    Set objFiles = objFso.GetFolder(sFolder).Files


    'Loop through each file in the folder
        If InStr(1, objFile.Path, ".xls") > 0 Then
            Set wbTarget = Workbooks.Open(objFile.Path)
            With wbTarget.Worksheets(1)
                ary(0) = .Range("B8")
                ary(1) = .Range("B12")
                ary(2) = .Range("B14")
            End With
            
            With wbMaster.Worksheets(1)
                lRow = .Range("E" & .Rows.Count).End(xlUp).Offset(1, 0).Row
                .Range("E" & lRow & ":G" & lRow) = ary
            End With
            
            wbTarget.Close savechanges:=False
        End If


    'Request count of files in subfolders
    For Each objSubFolder In objSubFolders
        Consolidate objSubFolder.Path, wbMaster
    Next objSubFolder


    Exit For
    
'else statement goes here


End If
Next i
Next objFile


End Sub

 

[Joe4]

It looks like this is a continuation of other threads in which you had people helping you write this code. I have not done much with file scripting objects, so I don't think I am going to be able to be much help.

Note when posting questions to this forum, the general rule of thumb is this:
- If it is a follow-up question dependent upon the previous answer, it is usually best to post back to the original thread (after all, the ones who helped you come up with the code in the first place are probably the ones who will best be able to help you to modify it
- If it is a brand new question and not dependent upon the previous reply, then you can post it to a new thread (but realize that you need to post all pertinent information - users should not be expected to go back and look at your other threads to understand what you are trying to do)

 

[Joe9238]

I've got this from various sources on google. I've made threads based on various things concerning this but none had any real significance so this is as good as I can do.