Compile error (type mismatch)

beginner999 · Jun 16, 2014

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 · Jun 16, 2014

It might help if you explain what you are trying to do ...

- What's in cell H1? I1? J1? Are these dates, or numbers?

- Should the calculation be made only if Cell D1 is "NOT IN IEDMS" or "IN DMFAS", i.e. are there other possibilities?

- Your code loop is trying to set the same value for each of the cells in the range A1:A4876. Is this what you want? What formula or value are you trying to set?

beginner999 · Jun 16, 2014

Thank you for response Sir. Im just newbie here in excel. Is it my logical condition is wrong?

- H1 & I1 & J1 Are rane of dates. Yes, in number form. Im trying to compare them.

-In cell D1, they have the data of "NOT IN IEDMS and "IN DMFAS".
This is my formula code in array form:
=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))))))

I cannot add up the year 2015 and 2016 because levels of nesting is reached so i try to do vba code for extraction of year.

- I want to set my formula in Range ("E1")

The only problem sir is how can i add up year 2015 and 2016 in the formula.

StephenCrump · Jun 17, 2014

If H1, I1 and J1 are in a valid date format, then it sounds like this formula will get you close to what you want?

E1: ="Jan-Dec" & Year(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1)))

If you want a different string for years <=2009, you'll just need to test for Year( ...) <=2009?

beginner999 · Jun 17, 2014

- Yes sir, if the dates: for example is H1=20081203, I1= 20070709 and J1= 19850812 it is belong to the year "2009below". This ranges would be the reference for the year.

I dont get your 2nd question sir.

Currently, Range ("E1") contain of this code, This formula is working

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))))))

If i continue to use nesting in excel, how can i add-up the year 2015 and 2016 in this formula? do you have some idea tho? Because im tohaving a hard-time to construct codes in vba. Thanks Sir in advance!

)

StephenCrump · Jun 17, 2014

I think this is close to what you want, and will accommodate any year, e.g. 2015, 2016 ...

Cells H1, I1 and J1 are dates, formatted as YYYYMMDD

D4: =YEAR(IF(OR(D1="IN DMFAS",D1="NOT IN IEDMS"),J1,MAX(H1,I1)))

D7: =IF(D4<2010,"2009below","Jan-Dec"&D4)

Excel 2010

	D	H	I	J
1	IN DMFAS	20081203	20070709	19850812
2
3	Year
4	1985
5
6	String
7	2009below

Sheet1

beginner999 · Jun 17, 2014

Excel 2010

A

B

C

D

E

F

G

H

I

J

1

IN DMFAS

=FORMULA

2

NOT IN IEDMS

2009below</SPAN>

20081203
</SPAN>

20070709

19850812

3

NOT IN DMFAS

Jan-Dec2011</SPAN>

20110604</SPAN>

4

NOT IN IEDMS</SPAN>

Jan-Dec2013</SPAN>

20130531</SPAN>

0</SPAN>

20130524</SPAN>

5

IN DMFAS</SPAN>

Jan-Dec2010</SPAN></SPAN>

20100713</SPAN></SPAN>

0</SPAN></SPAN>

20100713</SPAN></SPAN>

6

7

Sheet1

[/QUOTE]

beginner999 · Jun 17, 2014

Im sorry for the format, i dont know how to do it. I just copy and edit it from you. This is how the data inputted.

IN DMFAS = ALL REFERENCE RANGE ARE HAVE
NOT IN DMFAS = H1 & I1 ONLY
NOT IN IEDMS = J1 ONLY

beginner999 · Jun 17, 2014

The Extraction of date must be in E1. E1= FORMULA

beginner999 · Jun 17, 2014

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

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))))))

Compile error (type mismatch)

beginner999

New Member

Excel Facts

StephenCrump

MrExcel MVP

beginner999

New Member

StephenCrump

MrExcel MVP

beginner999

New Member

StephenCrump

MrExcel MVP

beginner999

New Member

beginner999

New Member

beginner999

New Member

beginner999

New Member

Similar threads

Forum statistics

Share this page

Compile error (type mismatch)

New Member

Excel Facts

MrExcel MVP

New Member

MrExcel MVP

New Member

MrExcel MVP

New Member

New Member

New Member

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock