Compile error (type mismatch)

[beginner999]

Hi Guys!
I'm using the looping condition for extraction of years. And heres my code. I having a trouble when i run it. Suggestion or clarification please. Thankss!

Sub GetLoans()

Dim i As String
Dim strYrFlag As String

Range("E1").Select
For i = 1 To 4876
Range("H1:J1") As Range

If Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value < 20100000
strYrFlag = "2009Below"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value < 20100000
strYrFlag = "2009Below"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20101231
strYrFlag = "Jan-Dec2010"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20101231
strYrFlag = "Jan-Dec2010"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20111231
strYrFlag = "Jan-Dec2011"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20111231
strYrFlag = "Jan-Dec2011"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20121231
strYrFlag = "Jan-Dec2012"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20121231
strYrFlag = "Jan-Dec2012"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20131231
strYrFlag = "Jan-Dec2013"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20131231
strYrFlag = "Jan-Dec2013"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20141231
strYrFlag = "Jan-Dec2014"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20141231
strYrFlag = "Jan-Dec2014"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20151231
strYrFlag = "Jan-Dec2015"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20151231
strYrFlag = "Jan-Dec2015"
ElseIf Range("D1").Value = ("IN DMFAS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20161231
strYrFlag = "Jan-Dec2016"
ElseIf Range("D1").Value = ("NOT IN IEDMS") Then
Cells(i, 1).Value = Range("H1:J1").Value >= 20161231
strYrFlag = "Jan-Dec2016"

End If
Next i

 

[StephenCrump]

A	B	C	D	E	F	G	H	I	J
				=FORMULA
			IN DMFAS	Jan-Dec2011			20110604
			NOT IN DMFAS	Jan-Dec2013			20130531	0	20130524
			NOT IN IEDMS	Jan-Dec2014					20140305

<tbody>
</tbody>

E1= =IF(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1))<20100000,"2009Below",
IF(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1))>20140000,"Jan-Dec2014",
IF(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1))>20130000,"Jan-Dec2013",
IF(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1))>20120000,"Jan-Dec2012","Jan-Dec"&VALUE(LEFT(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1)),4))))))

This formula doesn't produce these results in Column E. Are the results shown the correct results that you want to see?
So (I'm only guessing) ...

- If Column D contains "IN DMFAS" you want to use the date in column H?

- If column D contains "NOT IN IEDMS" you want to use the date in column J?

- If column D contains "NOT IN DMFAS" you want to use .. ?

- When do you use column I?

 

[beginner999]

Honestly speaking, i am not the one who do this program. Im only modifying to revised some certain codes. The file generate report in order to determine the status between two systems (DMFAS) and (IEDMS). It has a code written in VBA and others are formula array only. Like this problem that im telling you.

In your first question. Yes SIR, that formula is working as of year 2014 only. And my concern is how can i add year 2015 and 2016 in nesting form. Or should i change it into VBA code?

- If column D is "IN DMFAS" it refers to all ranges (H1,I1 and J1) because it is the new system that our using, and in the near future the IEDMS will be phase-out. So all the data in IEDMS will put to DMFAS. In short sir, the data in iedms is in dmfas already.

-If column D is "NOT IN IEDMS" it only refers in range (J1) meaning the data is no longer in this system. It is inputted in dmfas.

- If column D is "NOT IN DMFAS" it only refers in ranges (H1&I1) meaning the data is still in IEDMS.

- Column I is also a date for IEDMS. If it 0, meaning no dates inputted.

 

[StephenCrump]

As I understand it, and please correct me if I'm wrong ...

- The problem is that you want to have a formula in column E that returns a string like: Jan-DecYYYY
where YYY is the year number of some date in columns H, I or J

- YYYY might be 2010, 2011, 2012 ... 2015, 2016 or potentially beyond

- If YYY is before 2010, the string needs to be "2009Below"

If this is correct, you can use a relatively simple formula to determine the string. Provided the dates in columns H, I and J are in valid date format, you can use the YEAR() function to return YYYY. You don't need multiple IF()s to test for 2010, 2011, 2012 etc. Nor do you need VBA.

I am still not clear about which dates you want to use ...

-If column D is "NOT IN IEDMS", it sounds like a date will be provided only in column J, so presumably column J is the date to use?

- If column D is "NOT IN DMFAS" you say that dates will be provided in H and I, but your example shows dates in H and J. Which date(s) will be provided? Which date should the formula use e.g. MAX(H,J)?

- If column D is "IN DMFAS", you say that dates will be provided in H, I and J, but your example shows only H. Which date(s) will be provided? Which date should the formula use?

 

[beginner999]

- Yes sir. you're right. Can you show me some example formula to used that string. Im just started to study more in excel. Actually, this is my first job and i dont have yet experience. Sorry sir.


Regarding to those ranges (dates)

In summary, Column H and I named iedmsdate where in column D appears "NOT IN DMFAS" meaning all data is still in IEDMS system. Only Column H&I has a dates and J is blank
Column J named dmfasdate where in column D appears "NOT IN IEDMS" meaning all data is in DMFAS and only column J has a dates and column H&I is blank.
And "IN DMFAS" all columns has a dates inputted.

Sir, i think it is possible to compare all ranges in column D (NOT IN IEDMS, NOT IN DMFAS, and IN DMFAS)?

Regards

 

[beginner999]

I am still not clear about which dates you want to use ...

-If column D is "NOT IN IEDMS", it sounds like a date will be provided only in column J, so presumably column J is the date to use?

- If column D is "NOT IN DMFAS" you say that dates will be provided in H and I, but your example shows dates in H and J. Which date(s) will be provided? Which date should the formula use e.g. MAX(H,J)?

- If column D is "IN DMFAS", you say that dates will be provided in H, I and J, but your example shows only H. Which date(s) will be provided? Which date should the formula use?[/QUOTE]


- Yes sir. Right!

- Please dont mind my previous example. Im having a trouble doing it.
Column H and I is the provided dates. Where it name (iedmsdate)

-Yes sir, right. Please disregard the comparison about the columns date. We can compare all the ranges or based all the columns(H,I,J) in column D.

 

[StephenCrump]

It doesn't make things clearer if I ask specific questions ...

Which date(s) will be provided? Which date should the formula use e.g. MAX(H,J)?

... if you answer ...

Yes sir. Right!

It sounds like different dates will be provided depending on the value in column D, but we can always use the latest date in columns H, I, J? This means we don't need to test the value of column D at all?

Try the following formula in E1, and copy it down to E2, E3 etc.

Option 1: Values in columns H, I and J are dates, e.g. 20100101 is 1 January 2010 displayed in YYYYMMDD format, i.e. it has the value 40179.

=IF(YEAR(MAX(H1:J1))<2010,"2009below","Jan-Dec"&YEAR(MAX(H1:J1)))

Option 2: Values in columns H, I and J are in number format, e.g. 20100101 has the value 20100101.

=IF(MAX(H1:J1)<20100000,"2009below","Jan-Dec"&LEFT(MAX(H1:J1),4))

If neither option works for you, please give specific examples to show why not, including the answer that you want to see.

 

[beginner999]

Sir Option 2 is working but some other value in column (H,I,J) has 0 so if it has a cells of 0 the result is not be accurate. Kindly wait in my example. (print screen)

 

[beginner999]

E1 =IF(IF(OR(D7="IN DMFAS",D7="NOT IN IEDMS"),MAX(H7:J7))<20100000,"2009below","Jan-Dec"&LEFT(MAX(H7:J7),4))

D	E	F	G	H	I	J
NOT IN DMFAS <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Jan-Dec0 <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	NOT PART <TBODY> </TBODY><COLGROUP><COL span=2></COLGROUP>	NOT PART	0	0
NOT IN DMFAS <TBODY> </TBODY><COLGROUP><COL></COLGROUP>	Jan-Dec1968 <TBODY> </TBODY><COLGROUP><COL span=3><COL><COL></COLGROUP>	NOT PART	NOT PART	19680401	19680401

<TBODY>
</TBODY>

 

[beginner999]

When i used that formula. It appears the certain year in columns (H,I,J)

How can i come-up with only strings
2009Below
Jan-Dec2010
Jan-Dec2011
Jan-Dec2012
Jan-Dec2013
Jan-Dec2014
Jan-Dec2015
Jan-Dec2016

And how can i default "2009Below" if the year is null?

Big Thanks Sir!

 

[StephenCrump]

If you use my formula:

=IF(MAX(H1:J1)<20100000,"2009below","Jan-Dec"&LEFT(MAX(H1:J1),4))

don't you get the correct string "2009Below" for both the null dates and the 1968 dates?