I'm very new to using VBA within Excel (have only done a bit of VB5 a couple of years ago), but have a project I'm working on that requires me to use some VBA.
The basics of what I want to do are as follows:
- I have a worksheet with multiple rows of data that I want to process and insert into spreadsheet 1 of a new workbook.
- The processing (much of which seems to be working OK already individually) involves pulling out certain text and numbers from a text string in a cell, checking for < signs for values in other cells and assigning true/false, etc.
- The source spreadsheet has 14 columns while the target spreadsheet has to have 26 columns populated from data in the source spreadsheet (for instance, there are 9 columns that I have to determine whether the < sign exists and assign true/false [goes into 1 column as true or false] and then I insert the number value without the < sign into a second column)
- So, if the source spreadsheet has 15 rows, I need to loop through the cells in each row, perform actions on each cell, then add to the new spreadsheet, until each row has been processed and I end up with 15 rows of updated values in the new spreadsheet. I know which column each piece of data needs to be inserted into, but the row number will change.
So, some of the things I'm not sure how to do are:
1. how to reference the worksheets in different workbooks, so that the correct source data are used and the target destination is correct. To ensure that the correct worksheet is used, it also might be useful to use a function that returns the workbook and spreadsheet name, that can be referenced throughout the rest of the procedures.
2. I can't use specific references to individual cells, but need to loop through each cell in each row (or should I do it column by column?)
3. For the used cell range I am currently using a function that returns the number of rows and columns in the source spreadsheet, which I want to then use to ensure that the correct number of rows and colums are referenced from the source spreadsheet, and inserted into the target spreadsheet.
I am currently using Excel 2002, but the code needs to be compatible with Excel 2000, and possibly Excel 97 also. From what I've read, most VBA code written in 2002 is compatable with version 2000, and much will also work in 97.
Anyway, if anyone has any pointers on some or any of the above, that would be great.