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
– kooshy, Commented Dec 13, 2018 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
– BigBen, Commented Dec 13, 2018 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
– kooshy, Commented Dec 13, 2018 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
– Hidayat Ullah, Commented Dec 13, 2018 at 17:57

Tim Williams · Accepted Answer · 2018-12-13 18:25:12Z

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, 2018 at 18:25

answered Dec 13, 2018 at 18:16

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

BigBen Over a year ago

Your work in actually laying out the answer to the question deserves an upvote.

BigBen Over a year ago

And you could also simplify by moving Set c = activesheet... within the loop and eliminating the Set c = c.offset, just a thought.

Tim Williams Over a year ago

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

BigBen Over a year ago

I agree. Thank you for fleshing this out in actual code. Pity I can't upvote again.

Collectives™ on Stack Overflow

Dynamically selecting a specific cell using a loop

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related