Getting TYPE MISMATCH error in VBA

[Fusky]

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

 

[tygrrboi]

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?

 

[Fusky]

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

 

[Norie]

The problem is with Range("B2:B100") & Range("A2:GH2"), why are you trying to concatentate 2 ranges of different sizes?

 

[Fusky]

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,

 

[tygrrboi]

Do you perhaps mean

Application.Index(Range("A2:GH100"), Application.Match("ORDERS", Range("B2:B100"), 0), Application.Match("Country", Range("A2:GH2"), 0))

 

[Norie]

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?

 

[Fusky]

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

 

[Norie]

Does this work?

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

 

[tygrrboi]

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