CODEWITHSUNDEEP
excelvbaloops
Hidayat Ullah
Hidayat Ullah

Reputation: 157

Dynamically selecting a specific cell using a loop

Below is my code

Sub Append()
    'Append data from other files
    Path = "E:\NPM PahseIII\"
    Filename = Dir(Path & "*.xlsx")
    If InStr(Filename, ".") > 0 Then
        Filenamenoext = Left(Filename, InStr(Filename, ".") - 1)
    End If
    MsgBox Filenamenoext
    Range("A3").Select
    Do While Filename <> ""
        ActiveCell.Value = Filenamenoext
    Loop
End Sub

My problem is that as I've selected Range("A3").Select is hard coded, i want this selection to be done dynamically in loop such that when the first iteration of the loop start it should select Range("A3").Select & further select next cell in the next iteration. How can i achieve this? Edited See image below image

Upvotes: 0

Views: 580

Answers (1)

Tim Williams
Tim Williams

Reputation: 166391

Like this (untested):

Sub Append()

    Const FPath As String = "E:\NPM PahseIII\"
    Dim c As Range, Filename 

    'find the first empty cell in ColA
    Set c = activesheet.cells(rows.count, 1).end(xlup).offset(1, 0)

    Filename = Dir(FPath & "*.xlsx")
    Do While Filename <> ""
        c.Value = Split(Filename, ".")(0) 'note: problem if any of your file names have embedded periods...
        Set c = c.offset(1, 0)
        Filename = Dir()
    Loop

End Sub

Upvotes: 3

Related Questions