Range.Find returns nothing but syntax and data are correct

  • 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:

    1. with .sheets(3)
    2. frow = .cells.find("strings indicating data start from below").row+1 'Find 1st row of data
    3. lrow = .cells.find("strings indicating data rows is over").row-1 'Find last row of data
    4. for i = frow to lrow
    5. spcol = range("J" & i & ":O" & i).find("string to be found").column 'Find the column number containing specific string
    6. rawdatatable(j, 5) = .cells(i, (spcol + 9)).value 'Write the "9th column's value behind the column containing specific string to array
    7. j = j+1
    8. next i
    9. 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???

  • Did none of your books recommend using Option Explicit? Read this to learn why I always use it.

    I would think that this line does not require the dot before sheets(3)

    1. with .sheets(3)

    Also, using Sheets(3) will fail if anyone moves the position of the Tab.

    Referencing a Sheet in Excel VBA

    Attach your workbook and I'll look at it as soon as I can

  • Yes, I have learnt option explicit, I didn't use it but it doesn't hurt so I've added it to my code line 1 now

    Sorry I had to go away from my computer for past few days so I'm replying only now

    here attached the calculation file containing the macro, and a sample data file, please rename the file path in macro (I'll let users to choose from their folder but now I'm still debugging the macro)

    1. input the date range for query in sheet 1 of calculation file, hit the button on the right or run from VBE

    2. sub ArrayMonths creates an array of "list of months in query"

    3. function SelectFolder -- not in use until debugging over

    4. sub CreateFileList run thru specified folder, check 1st line in each file's sheet 1, grab the string to recognize if this file is of the month to be queried

    5. sub CreateRawDataTable grab data from each file picked by CreateFileList

    6. sub WriteTable2Sheet writes raw data table (array) to sheet 2

    7. sub GeneratePivot generates a pivot table in sheet 3

    Yes, I know it would be better if I use sheets("name") instead of sheets(3), however the sheets(3) changed name once before, if I've to use sheets("name") then another if else is required, just skip it for now

    the problem now is in sub CreateRawDataTable, Ln133~149, it just wont grab data from sheet(3), to be more precise, it sometimes ignore entire sheet(3) (e.g. this sample file), sometimes it ignore certain rows in sheet(3), I've not concluded the rule yet

    just to make the file running (so that I can proceed to write last 2 subs), I've added Ln133 since in original code spcol=range.find.column returns run time error 91 when it just don't grad the data

    I might have to explain the data file a little:

    1. for each sales, there are "local turnover"(sheet 1) and "split turnover"(sheet 3)

    2. split turnover meaning some invoices that's not entirely my, or my colleagues' credit, if the invoice is billed in my colleague's area, certain % of the turnover split-in to me, if the invoice is billed in my area (booked also in sheet 1), certain % split-out to my colleague(s)

    3. from columns K~O in sheet 3, list the sales persons entitled credit to this invoice, and each sales person's credit share is listed in columns P~T (after 2018/7 it becomes K-R and S-Z respectively