Type Miss Match error

[vmjan02]

I have this cod and it sould delete the entire column or columns in the list.

cell A2 onwards will have a list of column headers which need to be deleted,
my records starts for row no 8 and column E its E8

I am not sure as what is the problem, any suggestions.

Sub NokiaMDURawData()
   Dim Ary As Variant
   Dim i As Long
   'Dim i As String
   Dim Fnd As Range
   
   With Sheets("FTTX 050 Nokia MDU RawData")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
   End With
   For i = 2 To UBound(Ary) -- ''[B]I am getting the error here[/B]
      Set Fnd = Range("8:8").Find(Ary(i, 5), , xlValues, xlWhole, , , False, , False)
      If Not Fnd Is Nothing Then Fnd.EntireColumn.Delete
   Next i

End Sub

 

[duggie33]

Hi vmjan02,

It is hard to say for sure because I don't know what your data looks like, but I think your array reference in the Find is the problem. Your array is filled with values from column A so the reference should be Ary(i) or maybe Ary(i,1). I don't use arrays often enough to remember the syntax very well.

Hope that helps,

Doug

 

[vmjan02]

Hi Duggie33,

The column A and cell a2 to a18 has all the header names that need to be entire column, i am trying to loop all the headers
and my data startes for E8 and E8, F8 so on has the column headers.

 

[duggie33]

I am not following your layout, can you post some of your data with XL2BB? I still think your array reference is the issue. Have you stepped through the code and watched the Locals window to see how the array is referenced?

Doug

 

[vmjan02]

I am sharing the screen shot, i am sure this will help, in this case A2 is the header name and column G needs to be deleted.

 

[duggie33]

If you don't have very many columns to delete, maybe less than 100 and you only need to do it one time, I would do this somewhat manually. In my example below, in row 7, MATCH is used to check if the heading listed in column A. If true, it was found in column A, and you can select the column and delete it.

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Headers to Delete
2	kpi22
3	kpi2
4	kpi
5
6
7					FALSE	TRUE	FALSE	TRUE	FALSE	TRUE	FALSE
8					tps44	kpi2	tps66	kpi22	tps99	kpi	kpi33
9					1	2	3	4	5	6	7
10					5	6	7	8	9	10	11
11					9	10	11	12	13	14	15
12					13	14	15	16	17	18	19
13					17	18	19	20	21	22	23
14					21	22	23	24	25	26	27
15					25	26	27	28	29	30	31
16					29	30	31	32	33	34	35
17					33	34	35	36	37	38	39
18					37	38	39	40	41	42	43
19					41	42	43	44	45	46	47
20					45	46	47	48	49	50	51
21					49	50	51	52	53	54	55
22					53	54	55	56	57	58	59
23					57	58	59	60	61	62	63
24					61	62	63	64	65	66	67
25					65	66	67	68	69	70	71
26					69	70	71	72	73	74	75
27					73	74	75	76	77	78	79
Sheet1

Cell Formulas
Range	Formula
E7:K7	E7	=ISNUMBER(MATCH(E8,$A$2:$A$28,0))

If you want or need to do this in VBA, you need to straighten out your array syntax or approach it differently. I would create a range of the values in column A and then loop through the range with a For Each checking in row 8 with Find or Match.

Good luck,

Doug

 

[Akuini]

Maybe this:

Sub NokiaMDURawData_2()
   Dim Ary As Variant, x As Variant
   Dim i As Long
   Dim Fnd As Range
   
   With Sheets("FTTX 050 Nokia MDU RawData")
      Ary = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value2
      If Not IsArray(Ary) Then Ary = Array(.Range("A2")) 'if there's only 1 value
   End With

   For Each x In Ary
      Set Fnd = Range("8:8").Find(What:=x, LookIn:=xlValues, lookAt:=xlWhole, SearchOrder:=xlByRows, _
      SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
      If Not Fnd Is Nothing Then Fnd.EntireColumn.Delete
   Next

End Sub

 

[vmjan02]

thanks guys the code is working perfect, you guys are gem

 

[Akuini]

You're welcome, glad to help & thanks for the feedback.