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

 

[Fusky]

	Country
ORDERS	IN	WI	EG	AF	EA
ORDERS	100	500	67	15	75
ORDERS	12	34	90	287	744
ORDERS	129	8	799	567	63
ORDERS-PR	789	67	56	235	13
ORDERS-PR	45	990	11	234	178
ORDERS-PR	76	65	22	654	10
ORDERS-PR	200	47	32	54	876
ORDERS-PR	100	324	543	76	56

<tbody>
</tbody>

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,

 

[tygrrboi]

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.

 

[Fusky]

I have tried to put it directly to the cell as well,but not working

 

[tygrrboi]

This is your data...

	Country
ORDERS	IN	WI	EG	AF	EA
ORDERS	100	500	67	15	75
ORDERS	12	34	90	287	744
ORDERS	129	8	799	567	63
ORDERS-PR	789	67	56	235	13
ORDERS-PR	45	990	11	234	178
ORDERS-PR	76	65	22	654	10
ORDERS-PR	200	47	32	54	876
ORDERS-PR	100	324	543	76	56

<tbody>
</tbody>

What do you expect to see in H3? Be specific.

 

[Fusky]

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

 

[tygrrboi]

Perhaps something like this?

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

 

[tygrrboi]

sorry i forgot the IF part of your sum...

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

 

[Fusky]

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,

 

[tygrrboi]

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?

 

[tygrrboi]

You are getting 0 because there is no row that has just "ORDERS"

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERS_PR

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

ORDERSRfcst

<tbody>
</tbody>

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.