Compile error (type mismatch)

B

beginner999

New Member
Joined
Jun 16, 2014
Messages
33
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
 
Last edited:
Sort by date Sort by votes
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?
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
- 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! :))
 
Upvote 0
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
ABCDEFGHIJ
IN DMFAS

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20081203[/TD]
[TD="align: right"]20070709[/TD]
[TD="align: right"]19850812[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Year[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]1985[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]String[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]2009below[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1​
 
Upvote 0
Excel 2010
A
B
C
D
E
F
G
H
I
J
1
[TABLE="width: 183"]
<TBODY>[TR]
[TD]IN DMFAS

<TBODY>
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</TBODY><COLGROUP><COL></COLGROUP>
[/TD]
[TD="align: right"]=FORMULA
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[TD="align: right"]
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]NOT IN IEDMS[/TD]
[TD="align: right"]2009below</SPAN>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20081203
</SPAN>[/TD]
[TD="align: right"]20070709
[/TD]
[TD="align: right"]19850812
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]NOT IN DMFAS
[/TD]
[TD="align: right"]Jan-Dec2011</SPAN>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20110604</SPAN>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][TABLE="width: 642"]
<TBODY>[TR]
[TD]NOT IN IEDMS</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="align: right"]Jan-Dec2013</SPAN>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20130531</SPAN>[/TD]
[TD="align: right"]0</SPAN>[/TD]
[TD="align: right"]20130524</SPAN>[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD][TABLE="width: 642"]
<TBODY>[TR]
[TD][/TD]
[TD]IN DMFAS</SPAN>
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[TD="align: right"]Jan-Dec2010</SPAN></SPAN>[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]20100713</SPAN></SPAN>
[/TD]
[TD="align: right"]0</SPAN></SPAN>
[/TD]
[TD="align: right"]20100713</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]
[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</TBODY>[/TABLE]
Sheet1​
[/QUOTE]
 
Upvote 0
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
 
Upvote 0
[TABLE="width: 500"]
<TBODY>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=FORMULA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]IN DMFAS[/TD]
[TD]Jan-Dec2011[/TD]
[TD][/TD]
[TD][/TD]
[TD]20110604</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NOT IN DMFAS</SPAN>[/TD]
[TD]Jan-Dec2013</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD]20130531</SPAN>[/TD]
[TD]0</SPAN>[/TD]
[TD]20130524</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]NOT IN IEDMS</SPAN>[/TD]
[TD]Jan-Dec2014</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]20140305</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY>[/TABLE]

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))))))
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Need help with VBA code prompting Compile error: Next without For
2
Replies
15
Views
561
Pi_Lover
P
D
  • Solved
Need help creating a formula based on VBA function!
Replies
5
Views
446
Trixterz
T
togenmoser
  • Solved
Help with VBA code error "For variable already in use"
2 3
Replies
22
Views
1K
MARK858
MARK858
M
  • Solved
exporting userform - doesn't work for other PC than mine
Replies
9
Views
529
Kyle123
Kyle123
R
  • Solved
VBA User selecting file now the code errors Type Mismatch
2 3 4
Replies
30
Views
3K
Alex Blakenburg
A

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top