Getting TYPE MISMATCH error in VBA

F

Fusky

New Member
Joined
Nov 17, 2016
Messages
15
hiii, i have a code something like this :


Public Sub Match()




ThisWorkbook.Sheets("Sheet1").Activate


Range("Data!H8") = Application.Sum(Application.Index(Range("A:GH"), 0, Application.Match("ORDERS" & "Country", Range("B2:B100") & Range("A2:GH2"), 0)))


End Sub


When i run this code i'm getting a error "Type Mismatch", can anybody help me with this, Thankss
 
Sort by date Sort by votes
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Country[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]IN[/TD]
[TD]WI[/TD]
[TD]EG[/TD]
[TD]AF[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]67[/TD]
[TD]15[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]12[/TD]
[TD]34[/TD]
[TD]90[/TD]
[TD]287[/TD]
[TD]744

[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]129[/TD]
[TD]8[/TD]
[TD]799[/TD]
[TD]567[/TD]
[TD]63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]789[/TD]
[TD]67[/TD]
[TD]56[/TD]
[TD]235[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]45[/TD]
[TD]990[/TD]
[TD]11[/TD]
[TD]234[/TD]
[TD]178[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]76[/TD]
[TD]65[/TD]
[TD]22[/TD]
[TD]654[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]200[/TD]
[TD]47[/TD]
[TD]32[/TD]
[TD]54[/TD]
[TD]876[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]100[/TD]
[TD]324[/TD]
[TD]543[/TD]
[TD]76[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The numbers i getting whenn i used the formula were not really the sum of the columns thats what i meant by weird. Sheet1 is the file with all the datas are in,"Data" is the sheet where i would like to get the data from Sheet1, The data is large thats why i would like to use vba,
 
Upvote 0

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You are using VBA to just put a formula into a cell... It has the same effect as actually putting the formula into the cell.
 
Upvote 0
This is your data...

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Country[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]IN[/TD]
[TD]WI[/TD]
[TD]EG[/TD]
[TD]AF[/TD]
[TD]EA[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]100[/TD]
[TD]500[/TD]
[TD]67[/TD]
[TD]15[/TD]
[TD]75[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]12[/TD]
[TD]34[/TD]
[TD]90[/TD]
[TD]287[/TD]
[TD]744
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS[/TD]
[TD]129[/TD]
[TD]8[/TD]
[TD]799[/TD]
[TD]567[/TD]
[TD]63[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]789[/TD]
[TD]67[/TD]
[TD]56[/TD]
[TD]235[/TD]
[TD]13[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]45[/TD]
[TD]990[/TD]
[TD]11[/TD]
[TD]234[/TD]
[TD]178[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]76[/TD]
[TD]65[/TD]
[TD]22[/TD]
[TD]654[/TD]
[TD]10[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]200[/TD]
[TD]47[/TD]
[TD]32[/TD]
[TD]54[/TD]
[TD]876[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]ORDERS-PR[/TD]
[TD]100[/TD]
[TD]324[/TD]
[TD]543[/TD]
[TD]76[/TD]
[TD]56[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


What do you expect to see in H3? Be specific.
 
Upvote 0
I need to SUM numbers in a column if the row heading = ORDERS and the column header = IN. the formula to "scan" the column headings, find the one that says "IN", and then spit back the sum of those numbers in column heading named ("IN") with a row heading of "ORDERS".


and similarly want to get the sum for the other column headings like WI,EG etc

 
Upvote 0
Perhaps something like this?

Code: 
Sub SumCountry()


Dim sCountry As String
Dim colnum As Long

sCountry = "IN" [COLOR=#ff0000]'you can set this via a cell reference, or a user input.. or even inside a loop if you change Sheets("Data").Range("H8") to something with the loops variable...[/COLOR]


With Sheets("Sheet1")
   colnum = Application.Match(sCountry, .Range([COLOR=#ff0000]"A1:G1"[/COLOR]), 0)[COLOR=#ff0000] 'your range of the header row with all the country names... always starting with A...[/COLOR]
   Sheets("Data").Range("H8").Value = Application.Sum(.Range(.Cells(2, colnum), .Cells(100, colnum)))
End With


End Sub
 
Last edited:
Upvote 0
sorry i forgot the IF part of your sum...

Code: 
Sub SumCountry()


Dim sCountry As String
Dim colnum As Long

sCountry = "IN" [COLOR=#ff0000]'you can set this via a cell reference, or a user input.. or even inside a loop if you change Sheets("Data").Range("H8") to something with the loops variable...[/COLOR]


With Sheets("Sheet1")
   colnum = Application.Match(sCountry, .Range([COLOR=#ff0000]"A1:G1"[/COLOR]), 0)[COLOR=#ff0000] 'your range of the header row with all the country names... always starting with A...[/COLOR]
   Sheets("Data").Range("H8").Value = Application.[COLOR=#0000ff]SumIf(.Range("B2:B100"), "ORDERS", .[/COLOR]Range(.Cells(2, colnum), .Cells(100, colnum)))
End With


End Sub
 
Upvote 0
Thanks for ur efforts tygrrboi :D , I have tried both codes the first one working correctly except it didnt consider the ORDERS. but when i try the second part it is return Value of 0,
 
Upvote 0
I am not sure why you are getting 0...

It it working fine on my end.

Could you maybe upload a sample file onto a filesharing website like Dropbox or Google Drive so I can see what the issue is with your actual data?
 
Upvote 0
You are getting 0 because there is no row that has just "ORDERS"

[TABLE="width: 77"]
<tbody>[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERS_PR[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
[TR]
[TD]ORDERSRfcst[/TD]
[/TR]
</tbody>[/TABLE]


You will either need to change it to ORDERS_PR or ORDERSRfcst....

As for your columns... I do not know what they mean... there seem to be two headers. Perhaps you can work it out once you see how SUMIF works when you put in the correct condition, not just "ORDERS"...




Additionally: Sharing your file link in the post itself instead of in a private message will allow for others to possibly help you. I try my best but I'm by no stretch of the imagination as good as some of the other folks on here.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Run time error 13 type mismatch
Replies
1
Views
401
Domenic
D
W
  • Solved
Another Type Mismatch reading text file
Replies
3
Views
340
myall_blues
myall_blues
K
  • Locked
  • Question Question
Vba code for User history log which is giving me a run-time error "13" mismatch
Replies
2
Views
138
Fluff
Fluff
E
  • Solved
VBA to remove Apostrophes works but also gives Run-time error 13. Unable to work out why?
2 3
Replies
22
Views
957
Event2020
E
KlausW
  • Solved
Excel VBA Error - Unable to get the Insert property of the Picture Class
Replies
7
Views
303
KlausW
KlausW

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

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