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

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related