Getting TYPE MISMATCH error in VBA

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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Range wont take sheet names.

You might want to do something like

Sheets("Data").Range("H8")


also I am not sure the MATCH function works like that.... What are you trying to match?
 
Upvote 0
Hii, thanks for ur reply, i have tried changing the codes as ur suggestion but unfortunately i am still getting the same error :( ., Actually iam trying to get the summary table , so iam trying to get sum of certain columns in another sheet by looking its column and row heading instead of Cell reference, here ORDERS are my rows and Country is my column, THankss
 
Upvote 0
The problem is with Range("B2:B100") & Range("A2:GH2"), why are you trying to concatentate 2 ranges of different sizes?
 
Upvote 0
thanks for ur reply Norrie, The range is different because the country is in a range between A2:GH2 and ORDERS is in a range of B2:B100, total range will be A:GH as i put in the INDEX function,
 
Upvote 0
Do you perhaps mean

Application.Index(Range("A2:GH100"), Application.Match("ORDERS", Range("B2:B100"), 0), Application.Match("Country", Range("A2:GH2"), 0))
 
Upvote 0
Shouldn't you have 2 Match functions then, one for row (ORDERS?) and one for column (Country?)?

Do you have a similar formula that works on a sheet?
 
Upvote 0
Public Sub Match()




ThisWorkbook.Sheets("Sheet1").Activate


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


End Sub
i have tried to use two match functiions something similar to this but, it is showing weird numbers
 
Upvote 0
Does this work?
Code:
Public Sub Test()

    ThisWorkbook.Sheets("Sheet1").Activate

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

End Sub
 
Last edited:
Upvote 0
Not sure what you mean by "weird"...

Are you looking for the word "ORDERS" and the word "Country " (with a space)?

Or are these supposed to be one of the items in that category? Like "T-shirts" or "Venezuela"



Another question... Does Sheet1 exist? I ask because your Data sheet is specifically defined... is there a sheet thats actually called Sheet1? Or do you mean Sheets(1)?


I think it would be helpful to us if you described your sheets (names, what cells contain data, etc) And What you want the macro to achieve...

Looking at your code without any background as to what it should be doing... It seems a bit useless to me. Why not just put the formula in the cell H3 on Sheet Data and be done with it? You won't need to worry about VBA at all...
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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