SureShotUK
New Member
- Joined
- Mar 28, 2012
- Messages
- 5
Hello All
I am a VBA virgin (pretty much) but I think that I have a requirement that can only be solved using VBA so any help you can offer would be great.
I receive a spreadsheet (.xls extension) every workday and the filename is made up of the date in yyyymmdd format.
I have the individual excel spreadsheets for each day going back years.
The format of these spreadsheets changes every year or so!!
I would like to do something like a MATCH() formula to find out what row a specific value is located.
I have the following details in my new and separate spreadsheet where I would like to record the row numbers.
Date (eg 02/01/2008 ), Filepath (eg 'C:\Documents and Settings\My Documents\2008\[20080102.xls]Sheet1'!B:B ), Lookup value (eg Apple )then a Blank column for the row number to be posted.
I then have 1085 more rows (one for each working day since Jan 2008) where the filepaths are identical apart from the year (folder) and the filename.
I don't mind if the automated procedure when started takes a while as I intend to only do this once then copy and paste special values once I have them, but I don't want to have to open over 1000 spreadsheets (and growing day by day).
In my own head, the VBA would call up the range (column B in the desired woorkbook) then look for the relevant cell with the value in it without actually opening the workbook (if this is even possible). Then return the row number that it found that value on in the target workbook. As I say VBA virgin.
Any help will be grand and it would be easy if I could use VLOOKUP with INDIRECT on closed workbooks
Thanks
I am a VBA virgin (pretty much) but I think that I have a requirement that can only be solved using VBA so any help you can offer would be great.
I receive a spreadsheet (.xls extension) every workday and the filename is made up of the date in yyyymmdd format.
I have the individual excel spreadsheets for each day going back years.
The format of these spreadsheets changes every year or so!!
I would like to do something like a MATCH() formula to find out what row a specific value is located.
I have the following details in my new and separate spreadsheet where I would like to record the row numbers.
Date (eg 02/01/2008 ), Filepath (eg 'C:\Documents and Settings\My Documents\2008\[20080102.xls]Sheet1'!B:B ), Lookup value (eg Apple )then a Blank column for the row number to be posted.
I then have 1085 more rows (one for each working day since Jan 2008) where the filepaths are identical apart from the year (folder) and the filename.
I don't mind if the automated procedure when started takes a while as I intend to only do this once then copy and paste special values once I have them, but I don't want to have to open over 1000 spreadsheets (and growing day by day).
In my own head, the VBA would call up the range (column B in the desired woorkbook) then look for the relevant cell with the value in it without actually opening the workbook (if this is even possible). Then return the row number that it found that value on in the target workbook. As I say VBA virgin.
Any help will be grand and it would be easy if I could use VLOOKUP with INDIRECT on closed workbooks
Thanks
Last edited:
