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"]

Company ID

Company Name

Relevant Financial Year 1

Relevant Financial Year 2

Relevant Financial Year 3

1121

AA

Dec-12

Dec-13

Dec-14

2231

AB

Dec-14

Dec-15

Dec-16

3567

AC

Jan-12

Jan-13

Jan-15

4901

AD

Feb-14

Feb-15

Feb-16

5

AE

Jan-12

Jan-13

Jun-13

6

AF

7

AG

[/TABLE]

Dim rRange As Range
            Dim ComID As Integer
            Dim S1 As Worksheet
          
            Set S1 = ActiveWorkbook.Worksheets("Def_Sheet")
            ComID = S3.Cells(I, Col_CID).Value
            S1.Select
            Set rRange = S1.Range("U4", Range("U4").End(xlDown))
            
            'save the first relevant date as YR(1) in the date format as per the table above
            rRange.Find(What:=ComID, After:="U4", LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 2) = YR(1)
            
            rRange.Find(What:=ComID, After:="U4", LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 3) = YR(2)
            
            rRange.Find(What:=ComID, After:="U4", LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 4) = YR(3)


Num_Year=3            
For J = 1 To Num_Year
                If S3.Cells(I, Col_CID + 3) = YR(J) Then
                    'display FYE
                    S4.Cells(Row_Curr + 21, Col_Curr + J - 1) = S3.Cells(I, Col_CID + 4)

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".

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

Quote from Batman;768134

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.

Display More

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.

Dim rRange As Range
            Dim ComID As Integer
            Dim S1 As Worksheet
            Dim S3 As Worksheet






            'Please help: I am not sure if this is the right way to define an array
            Dim YR(3) As Date
            Dim I as Integer
            Set S1 = ActiveWorkbook.Worksheets("Def_Sheet")
            Set S3 = ActiveWorkbook.Worksheets("Input")
            ComID = S3.Cells(I, 1).Value


'I cant get this part of the code to work too. Please suggest a solution to this code if you could. Error given was Error 1004: Range of Object_Worksheet Failed
            Set rRange = S1.Range("U4", Range("U4").End(xlDown))
            
            For I = 1 To 999
            
            'save the first relevant date (I need YR(1) to be saved in the form of Dec-12 for instance for ComID of 1615)
            rRange.Find(What:=ComID, After:=S1.Range("U4"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 2) = YR(1)
            
            'save the second relevant date (I need YR(2) to be saved in the form of Dec-13 for instance for ComID of 1615)
            rRange.Find(What:=ComID, After:=S1.Range("U4"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 3) = YR(2)
            
            'save the third relevant date (I need YR(3) to be saved in the form of Dec-14 for instance for ComID of 1615)
            rRange.Find(What:=ComID, After:=S1.Range("U4"), LookIn:=xlValues, LookAt:= _
            xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False).Offset(0, 4) = YR(3)
            
            '****a bunch of other codes which needs the dates captured in YR(1), YR(2) and YR(3) to run accordingly
            
            Next I