I'm newbie to VBA, read 6 days of books and start writing a macro for (estimated) 200~250lines
background: there are monthly Excel reports in a folder, report format is relatively fixed, there are 2 sheets in each reports contains key data, the 2 sheets are of different format
the function of this macro is to retrieve specific data (data in fixed columns) from both sheets and from reports in user specified time period
what's wrong now, is a step to locate a column number:
- with .sheets(3)
- frow = .cells.find("strings indicating data start from below").row+1 'Find 1st row of data
- lrow = .cells.find("strings indicating data rows is over").row-1 'Find last row of data
- for i = frow to lrow
- spcol = range("J" & i & ":O" & i).find("string to be found").column 'Find the column number containing specific string
- rawdatatable(j, 5) = .cells(i, (spcol + 9)).value 'Write the "9th column's value behind the column containing specific string to array
- j = j+1
- next i
- end with
range("J" & i & ":O" & i).find("string to be found") returns Nothing hence run time error 91 happens (I found this by using msg range("J" & i & ":O" & i).find("string to be found") is nothing --- which tells me TRUE)
funny part is, I copied the original data from original Excel to a blank new Excel file, and tried this range("J" & i & ":O" & i).find("string to be found").column statement, it works perfectly!!
what could have gone wrong? please help???