Dynamically selecting a specific cell using a loop

Question

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

what's the range you want to select in a loop? Include an image and show where you want the values updated — kooshy, Dec 13 '18 at 17:34
Use `Range.End(xlUp).Offset(1)` - basically find the last populated cell in the column and offset 1 row. Many examples on SO of how to do this. — BigBen, Dec 13 '18 at 17:47
Starting in A3 to where? You still haven't answered the question. What cells you want to update in this LOOP. You want to start with A3 - I get that, but how many cells you want to update — kooshy, Dec 13 '18 at 17:48
@kooshy as in my loop there 6 files to read, i want that with every file read, cell range should go next cell. in order to write each file name in the separate cell. — Hidayat Ullah, Dec 13 '18 at 17:57
listen maybe someone else can help. i don't understand what you want to do. Good luck. — kooshy, Dec 13 '18 at 18:01
See [this answer](https://stackoverflow.com/a/38882823/9245853), in relation to my previous comment. — BigBen, Dec 13 '18 at 18:03
Yes but it solves your problem to find the last row and then use `Offset`. Then you don't have to hard code a range, or use `ActiveCell` or `Select`. — BigBen, Dec 13 '18 at 18:07
@HidayatUllah see the answer posted. Note the use of `.end(xlUp)` and `Offset` — BigBen, Dec 13 '18 at 18:21

Tim Williams · Answer 1 · 2018-12-13T18:25:12.813

3

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

edited Dec 13 '18 at 18:25

answered Dec 13 '18 at 18:16

Tim Williams

154,628
8
97
125

Your work in actually laying out the answer to the question deserves an upvote. – BigBen Dec 13 '18 at 18:20
And you could also simplify by moving `Set c = activesheet...` within the loop and eliminating the `Set c = c.offset`, just a thought. – BigBen Dec 13 '18 at 18:27
Yes could be a few lines shorter but wanted to show how to (1) get a reference to the first empty cell (2) "move" that reference using offset(). All without select/activate – Tim Williams Dec 13 '18 at 18:30
I agree. Thank you for fleshing this out in actual code. Pity I can't upvote again. – BigBen Dec 13 '18 at 18:31

Dynamically selecting a specific cell using a loop

1 Answers1