Runtime Error "Type Mismatch"

S

SBF12345

Well-known Member
Joined
Jul 26, 2014
Messages
614
Greetings,

I am receiving a type mismatch error on the following line. Not sure why this is occurring.

Are there limitations on the properties that I can establish within the row and column values? For instance is "A+3" as a row value not acceptable when being compared with an integer variable alone "A".


Code: 
If Cells(A, "E").Value > Cells((A + 1), "E").Value And Cells((A + 2), "E").Value And Cells((A + 3), "E").Value And Cells((A + 4), "E").Value And Cells((A + 5), "E").Value And Cells((A + 6), "E").Value And Cells((A + 7), "E").Value And Cells((A + 8), "E").Value And Cells((A + 9), "E").Value And Cells((A + 10), "E").Value And Cells((A - 1), "E").Value And Cells((A - 2), "E").Value And Cells((A - 3), "E").Value And Cells((A - 4), "E").Value And Cells((A - 5), "E").Value And Cells((A - 6), "E").Value And Cells((A - 7), "E").Value And Cells((A - 8), "E").Value And Cells((A - 9), "E").Value And Cells((A - 10), "E").Value Then

Thanks, SBF12345
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Sort by date Sort by votes
Can you please explain exactly what you are trying to do here. Are you really trying to Cells(A, "E").Value against a concatenation of 20 cell values?

You will get a type mismatch if any of those 20 cells has text in it.

...and post your full code please.
 
Last edited:
Upvote 0
the values in the column "E" are integer values. They exist within the framework of a moment to moment time series. I am trying to identify points within that random set of integers moments that are greater than moments within 10 moments before and after the test moment. This line exists within the script below...

Also, is there maybe a more concise way to write this line with the error?

Code: 
Sub TRENDSHEET()

Dim ws As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B As Integer 
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer
Dim AA As Range
Dim BB As Range
Dim CC As Range
Dim DD As Range
Dim R1 As Range
Dim R2 As Range








Set TA = Workbooks("COMM_TREND.xls")
Set PA = Workbooks("COMM_PA.xls")








For Each ws In PA.Worksheets


A = ws.Cells(Rows.Count, "A").End(xlUp).Row


    Do Until A = 2
    
        'Does row contain a MAX prominent point?
        If Cells(A, "E").Value > Cells((A + 1), "E").Value And Cells((A + 2), "E").Value And Cells((A + 3), "E").Value And Cells((A + 4), "E").Value And Cells((A + 5), "E").Value And Cells((A + 6), "E").Value And Cells((A + 7), "E").Value And Cells((A + 8), "E").Value And Cells((A + 9), "E").Value And Cells((A + 10), "E").Value And Cells((A - 1), "E").Value And Cells((A - 2), "E").Value And Cells((A - 3), "E").Value And Cells((A - 4), "E").Value And Cells((A - 5), "E").Value And Cells((A - 6), "E").Value And Cells((A - 7), "E").Value And Cells((A - 8), "E").Value And Cells((A - 9), "E").Value And Cells((A - 10), "E").Value Then
 
Upvote 0
Code: 
Sub TRENDSHEET()



Dim ws As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer
Dim AA As Range
Dim BB As Range
Dim CC As Range
Dim DD As Range
Dim R1 As Range
Dim R2 As Range








Set TA = Workbooks("COMM_TREND.xls")
Set PA = Workbooks("COMM_PA.xls")








For Each ws In PA.Worksheets


A = ws.Cells(Rows.Count, "A").End(xlUp).Row


    Do Until A = 2
    
                If Cells(A, "E").Value > Cells((A + 1), "E").Value And Cells((A + 2), "E").Value And Cells((A + 3), "E").Value And Cells((A + 4), "E").Value And Cells((A + 5), "E").Value And Cells((A + 6), "E").Value And Cells((A + 7), "E").Value And Cells((A + 8), "E").Value And Cells((A + 9), "E").Value And Cells((A + 10), "E").Value And Cells((A - 1), "E").Value And Cells((A - 2), "E").Value And Cells((A - 3), "E").Value And Cells((A - 4), "E").Value And Cells((A - 5), "E").Value And Cells((A - 6), "E").Value And Cells((A - 7), "E").Value And Cells((A - 8), "E").Value And Cells((A - 9), "E").Value And Cells((A - 10), "E").Value Then
    
    
        Set AA = ws.Cells(A, "E")
        Set DD = ws.Cells(A, "A")
        G = AA.Row
        H = ws.Cells(A, "A").Row
    
        TA.ws.Range("A1").Select
        Selection.End(xlDown).Offset(1, 0).Select
        Selection.Value = "ACTIVE"
    
        DD.Copy
        TA.ws.Range("A1").Select
        Selection.End(xlDown).Offset(0, 1).Select
        Selection.PasteSpecial Paste:=xlValues
        
        AA.Value.Copy
        TA.ws.Range("A1").End(xlDown).Offset(0, 2).Select
        Selection.PasteSpecial Paste:=xlValues
        
        
        A = E
    
    
    Do Until E - 10 = 2
                      
        I = Range(Cells(2, "A")).Row
                      
        Set CC = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))
                      
        D = E - 10
       
        Set R1 = PA.ws.Range(Cells(G, "A")).Row
        
            For Each R1 In CC
            
            H = Range(Cells(E, "A")).Row
       
            BB = PA.ws.Range(Cells(G, "A"), Cells(I, "A"))
                   
            Set R2 = PA.ws.Range(Cells(G, "A")).Row
            
                For Each R2 In BB
       
                B = (Cells(D, "E").Value - Cells(G, "E").Value) / (Cells(D, "A").Value - Cells(G, "A").Value)
    
                C = PA.ws.Range(Cells(D, "A"), Cells(G, "A")).Rows.Count
                
                I = Range(Cells(D, "E").Value - Cells(G, "E").Value)
        
                F = A - (A - H)
            
                J = PA.ws.Range(Cells(H, "A").Value - Cells(G, "A").Value).Rows.Count
            
                If PA.ws.Range.Cells(H, "E").Value > AA.Value + (B * F) And F < C + 10 Then
                  
                ElseIf PA.ws.Range.Cells(H, "E").Value > AA.Value + (B * F) And F > C + 10 Then
                
                TA.ws.Range("A1").End(xlDown).Offset(1, 0).Select
                Selection.Value = "ACTIVE"
                
                PA.ws.Range.Cells(H, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 1).Select
                Selection.Paste PasteSpecial:=xlValues
                
                AA.Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 2).Select
                Selection.Paste PasteSpecial:=xlValues
                
                PA.ws.Range.Cells(D, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 3).Select
                Selection.Paste PasteSpecial:=xlValues
                
                PA.ws.Range.Cells(D, "E").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 4).Select
                Selection.Paste PasteSpecial:=xlValues
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 5).Value = C
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 6).Value = I
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 7).Value = B
                
                TA.ws.Range("A1").End(xlDown).Offset(0, 8).Select
                Selection.Paste PasteSpecial:=xlValues
                PA.ws.Range.Cells(H, "A").Value.Copy
                TA.ws.Range("A1").End(xlDown).Offset(0, 9).Select
                Selection.Paste PasteSpecial:=xlValues
                
                ElseIf PA.ws.Range.Cells(H, "E").Value < AA.Value + (B * F) Then
                
                End If
                
                Next R2
                    
        Next R1
        
        
        E = E - 1
        
  Loop
      
        Else
            A = A - 1
        End If
    
  Loop
        
        
  Next ws      
            
        
        'Else   
    


    




End Sub
 
Upvote 0
I am trying to identify points within that random set of integers moments that are greater than moments within 10 moments before and after the test moment
Click to expand...

Ok, to try and get to the bottom of this. Referring to the code in post number 1 and the quote above are you trying to test that Cells(A, "E").Value is greater than each individual value and then if they are all less than Cells(A, "E").Value carry out the then statement?

I am asking because as you have it written it is if you are trying to concatenate all the values for a single test.
i.e. if a = 1, b = 2, c= 3 and d = 4 then the test would be Cells(A, "E").Value > 1234 which I don't believe is right.

Also is that part of the code also supposed to be running on the ws sheet?

As for your Dim statements you could change

Code: 
Dim ws As Worksheet
Dim PA As Workbook
Dim TA As Workbook
Dim A As Integer
Dim B As Integer 
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim F As Integer
Dim G As Integer
Dim H As Integer
Dim I As Integer
Dim J As Integer
Dim K As Integer
Dim L As Integer
Dim M As Integer
Dim N As Integer
Dim AA As Range
Dim BB As Range
Dim CC As Range
Dim DD As Range
Dim R1 As Range
Dim R2 As Range

to

Code: 
Dim ws As Worksheet, PA As Workbook, TA As Workbook
Dim A As Long, B As Long, C As Long, D As Long
Dim E As Long, F As Long, G As Long, H As Long
Dim I As Long, J As Long, K As Long, L As Long
Dim M As Long, N As Long
Dim AA As Range, BB As Range, CC As Range, DD As Range
Dim R1 As Range, R2 As Range

I also say that I think you will probably hit more issues later in your code as you aren't qualifying your ranges with their worksheets.
 
Last edited:
Upvote 0
Ok, to try and get to the bottom of this. Referring to the code in post number 1 and the quote above are you trying to test that Cells(A, "E").Value is greater than each individual value and then if they are all less than Cells(A, "E").Value carry out the then statement?
Click to expand...

Correct, I would like to test each individual value to cells(A, "E") and then carry out the if then statement if the statement is greater than the values to which it is compared.

I definitely don't want to concatenate cell values. I was under the assumption the "And" statement required all statements to be true in order to pass the test. Is there another way I can test relative referenced cell values?

Also is that part of the code also supposed to be running on the ws sheet?
Click to expand...
I'm not sure what you are referring to here, but I suspect the answer is no. The only ws printed output is assigned to the workbook TA and includes descriptive measures for various qualifying inputs.

I also say that I think you will probably hit more issues later in your code as you aren't qualifying your ranges with their worksheets.
Click to expand...

Can you give an example of qualifying ranges with ws? Are you referring to "R2" for instance?

Code: 
[COLOR=#333333]Set R2 = PA.ws.Range(Cells(G, "A")).Row[/COLOR]

Initially I had for each statements using integer values instead of the range values.

For instance

For Each integer1 in Range

as compared to the current

For each R2(range variable) in Range

I'm going to worry about this when the time comes:biggrin:
 
Upvote 0
Correct, I would like to test each individual value to cells(A, "E") and then carry out the if then statement if the statement is greater than the values to which it is compared.
Click to expand...

Then
Code: 
    If Cells(A, "E").Value > Cells((A + 1), "E").Value And Cells(A, "E").Value > Cells((A + 2), "E").Value And _
       Cells(A, "E").Value > Cells((A + 3), "E").Value And Cells(A, "E").Value > Cells((A + 4), "E").Value And _
       Cells(A, "E").Value > Cells((A + 5), "E").Value And Cells(A, "E").Value > Cells((A + 6), "E").Value And _
       Cells(A, "E").Value > Cells((A + 7), "E").Value And Cells(A, "E").Value > Cells((A + 8), "E").Value And _
       Cells(A, "E").Value > Cells((A + 9), "E").Value And Cells(A, "E").Value > Cells((A + 10), "E").Value And _
       Cells(A, "E").Value > Cells((A - 1), "E").Value And Cells(A, "E").Value > Cells((A - 2), "E").Value And _
       Cells(A, "E").Value > Cells((A - 3), "E").Value And Cells(A, "E").Value > Cells((A - 4), "E").Value And _
       Cells(A, "E").Value > Cells((A - 5), "E").Value And Cells(A, "E").Value > Cells((A - 6), "E").Value And _
       Cells(A, "E").Value > Cells((A - 7), "E").Value And Cells(A, "E").Value > Cells((A - 8), "E").Value And _
       Cells(A, "E").Value > Cells((A - 9), "E").Value And Cells(A, "E").Value > Cells((A - 10), "E").Value Then

or

Rich (BB code): 
    For Each xcell In Union(Range(Cells(A - 1, "E"), Cells(A - 10, "E")), Range(Cells(A + 1, "E"), Cells(A + 10, "E")))
        If xcell < Cells(A, "E").Value Then
            x = 0
        Else
            x = 1
        End If
        y = y + x
    Next
    If y = 0 Then
        'code if test passes
    Else
        'action if code fails
    End If

Can you give an example of qualifying ranges with ws? Are you referring to "R2" for instance?
Click to expand...
I am talking about lines like the below where neither Range or Cells is qualified with the worksheet (BTW, I stated worksheet not ws, I don't know whether it should be ws or another worksheet, either way it should be qualified with the appropriate worksheet). Asking for trouble down the line...
Code: 
I = Range(Cells(2, "A")).Row
 
Upvote 0
;) Or perhaps:

Code: 
If Cells(A, "E").Value > Application.Large(Cells(A - 10, "E").Resize(21), 2) Then
    'code if test passes
Else
    'action if code fails
End If
 
Upvote 0
@StephenCrump, will that not mean Cells(A, "E").Value will be testing against itself as it is in the middle of the range?
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
VBA - Runtime Error 6 - Overflow
Replies
5
Views
96
ststern45
S
afrazier1221
  • Solved
Help Needed With Error
Replies
7
Views
181
afrazier1221
afrazier1221
I
  • Solved
OR - formula is giving #value error
Replies
6
Views
119
etaf
etaf
W
  • Solved
Code for rows of cells returning reverse-order values.
Replies
5
Views
175
Worksong
W
J
  • Question
Repeat VBA for each column
Replies
3
Views
232
Alex Blakenburg
A

Forum statistics

Threads
1,215,219
Messages
6,123,678
Members
449,116
Latest member
HypnoFant

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