Selecting 3 relevant financial years for every company

  • I have a problem here and would like to seek help. Thank you in advance. As it's a long list of vba codes, I thought I would just illustrate the portion that I have problem with.


    S1: Definition Sheet
    S3: Input/Raw Data sheet
    S4: Display sheet (in the format required)


    I have a sheet with many companies and their relevant financial dates that I would like to display on another sheet (S4). All the inputs for the financial data are available in S3.


    I have a definition sheet(S1) with the list of the companies that I have and for each company, I have identified the financials that I need.


    Problem: I get an error saying there's a "Type Mismatch" and I have no idea what's wrong. If anyone could help, that would be very much appreciated. Thanks.


    [TABLE="class: grid, width: 500"]

    [tr]


    [td]

    Company ID

    [/td]


    [td]

    Company Name

    [/td]


    [td]

    Relevant Financial Year 1

    [/td]


    [td]

    Relevant Financial Year 2

    [/td]


    [td]

    Relevant Financial Year 3

    [/td]


    [/tr]


    [tr]


    [td]

    1121

    [/td]


    [td]

    AA

    [/td]


    [td]

    Dec-12

    [/td]


    [td]

    Dec-13

    [/td]


    [td]

    Dec-14

    [/td]


    [/tr]


    [tr]


    [td]

    2231

    [/td]


    [td]

    AB

    [/td]


    [td]

    Dec-14

    [/td]


    [td]

    Dec-15

    [/td]


    [td]

    Dec-16

    [/td]


    [/tr]


    [tr]


    [td]

    3567

    [/td]


    [td]

    AC

    [/td]


    [td]

    Jan-12

    [/td]


    [td]

    Jan-13

    [/td]


    [td]

    Jan-15

    [/td]


    [/tr]


    [tr]


    [td]

    4901

    [/td]


    [td]

    AD

    [/td]


    [td]

    Feb-14

    [/td]


    [td]

    Feb-15

    [/td]


    [td]

    Feb-16

    [/td]


    [/tr]


    [tr]


    [td]

    5

    [/td]


    [td]

    AE

    [/td]


    [td]

    Jan-12

    [/td]


    [td]

    Jan-13

    [/td]


    [td]

    Jun-13

    [/td]


    [/tr]


    [tr]


    [td]

    6

    [/td]


    [td]

    AF

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [tr]


    [td]

    7

    [/td]


    [td]

    AG

    [/td]


    [td][/td]


    [td][/td]


    [td][/td]


    [/tr]


    [/TABLE]


  • Re: Selecting 3 relevant financial years for every company


    Hi momosiew14,


    Welcome to the Ozgrid forum.


    In accordance with the rules you signed up to, please edit your post to add code tags to the VBA code. The easiest way to do this is to highlight the code section and click the # icon.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Selecting 3 relevant financial years for every company


    Hey pike,


    Sorry, perhaps I wasn't too clear with my question.


    S3 is an input sheet with raw data of all the companies' financial information. Col_CID is 1 and this refers to column 1 of the input sheet(S3) that stores the company ID for company I in this case.
    For instance, if the company ID is 351 (then ComID is 351), I want to be able to go to the definition sheet (S1), look through the range in S1 defined as rRange for the same company ID and look for the relevant date situated in Offset (0,2) and I want to reference that date as YR(1). I repeat this will other companies in the input sheet through a loop. For every company the YR(1) will be different. If you could advise me on the above, that would be greatly appreciated. Thank you in advance.



    'This refers to the company's ID
    ComID = S3.Cells(I, Col_CID).Value
    the error came in at saying that there's a "Type Mismatch".


    Code
    rRange.Find(What:=ComID, After:="U4", LookIn:=xlValues, LookAt:= _ 
    xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _ 
    MatchCase:=[COLOR=blue]False[/COLOR]).Offset(0, 2) = YR(1)
  • Re: Selecting 3 relevant financial years for every company


    It's still not clear whether your error is appearing on the code line starting 'ComID =' or 'rRange.Find'.


    Either way, there are too many variables that we can't see how you have declared them, and what values they might have when these parts of the code are run. Pike asked what the variable 'I' contained, and although you refer to it in your answer, you don't say exactly what value it would have. It should contain an integer to represent a row number.


    In the rRange... command, the contents of the 'After' argument will cause your Type Mismatch error. That needs to contain a VBA range reference, e.g.

    Code
    rRange.Find(What:=ComID, After:=S1.Range("U4"), ....


    It will need to refer to the same sheet as rRange. However, there is no indication of what type of variable YR is (it looks to be an array of some sort), and that could also be causing a problem.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

  • Re: Selecting 3 relevant financial years for every company



    Hey Batman, thanks for your help.


    This is actually a really long set of macros and I have just in fact pasted a portion of it that I have probem with here. I have attached the spreadsheet here and hopefully it's clearer. I want to be able to store the respective entity's 3 relevant financial year date that is available in the Def_Sheet(S1) and store it as YR(1), YR(2) and YR(3). I am not quite sure how to go about that but I have tried the code below and it doesnt work. If anyone could help with this, that would be great.


  • Re: Selecting 3 relevant financial years for every company


    Your definition of YR as an array will work, but there are a few points:


    > By default VBA will create a zero-base array, where the first item is number 0 (zero) and not 1
    > You need to assign values to the array
    > Your code is finding ComID, moving 2 cells to the right, and then trying to assign the (empty) value of the array to that cell.


    I suspect that you probably need to put YR(0) etc. before the = sign, rather than after it, as I assume you are trying to assign the value from the worksheet to the array, and not the other way round. However, as pike says, I think we need to understand more about the detail of what you are trying to do.

    [COLOR="Purple"]Regards,[/COLOR]
    [COLOR="Purple"]Batman.[/COLOR]

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!