Be Excellent To Each Other
https://www.beexcellenttoeachother.com/forum/

Excel VBA help
https://www.beexcellenttoeachother.com/forum/viewtopic.php?f=3&t=1742		 Page 1 of 1
Author:  Bobbyaro [ Tue Sep 02, 2008 13:23 ]
Post subject:  Excel VBA help
Right, I am not particularly good with this stuff.

I am trying to get all the files in a folder into a list in excel, as I am lazy.

I have an array which reads all the files in, and I can output all the files out, the one thing I can't seem to do, is output to a list. Ie, I can't move my activecell to the next in a range, I just overwrite the top cell in the list.

What I thought I could do is:

Code:
Function outputToSheet(listArray As Variant, arraySize As Integer) As Variant
   Dim fileName1 As String
   Dim counter As Integer
   Dim outputRange As Range
     
   counter = 1
   While (counter < arraySize)
   
      fileName1 = listArray(counter)
      Set outputRange = Range(Cells(counter, 1), Cells(arraySize, 1))
      ActiveCell.Value = fileName1 
      ActiveCell = outputRange.Next
      counter = counter + 1
     
   Wend
     


but this doesn't work, I realise I must be missing something fundamental about ranges and cells and selections or something, but any help would be appreciated, Thanks.
Author:  Cras [ Tue Sep 02, 2008 13:26 ]
Post subject:  Re: Excel VBA help
Instead of your:

Set outputRange = Range(Cells(counter, 1), Cells(arraySize, 1))
ActiveCell.Value = fileName1
ActiveCell = outputRange.Next

just do:

Cells(counter,1).value = filename1

That is if you're trying to do what I think you're trying to do. If you're doing the output one cell at a time, there's no point bothering with a range.
Author:  Bobbyaro [ Tue Sep 02, 2008 13:31 ]
Post subject:  Re: Excel VBA help
:DD That worked nicely!

Okay, why didn't mine work?
[/cheeky]
Author:  Cras [ Tue Sep 02, 2008 13:36 ]
Post subject:  Re: Excel VBA help
Because you were re-initialising the range every time you went through the loop.

You could have also tried moving the range initialisation outside the loop:

Set outputRange = Range(Cells(1, 1), Cells(arraySize, 1))
While (counter < arraySize)

fileName1 = listArray(counter)
ActiveCell.Value = fileName1
ActiveCell = outputRange.Next
counter = counter + 1

Wend

Which would probably have worked, although using a range is pretty much unnecessary in this instance.
Author:  Bobbyaro [ Tue Sep 02, 2008 13:48 ]
Post subject:  Re: Excel VBA help
Of course*! Okay, brilliant, thanks.

*we need a D'oh Dimlie.
Page 1 of 1 All times are UTC [ DST ]
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group
http://www.phpbb.com/