Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch - Match function
There is a slight problem with the asterixed section of the code (MATCH Function) if a value exists its all fine, but if there is an error I'm getting run-time error 13 - Type Mismatch Many Thanks Sub printtariq() On Error Resume Next Worksheets("Test").Select '1) Number the columns a = Cells.Find("WorkOrder").Column b = Cells.Find("CostC").Column c = Cells.Find("PaperSize").Column d = Cells.Find("PaperWeight").Column e = Cells.Find("NoBW").Column f = Cells.Find("NoColour").Column g = Cells.Find("printtype").Column h = Cells.Find("x").Column '2) Define last row and column lastrow = Range("A65500").End(xlUp).Row lastcol = Range("IV1").End(xlToLeft).Column '3) Strip letters from workorders and active workorders Cells(1, lastcol + 1) = "WoNo" Cells(1, lastcol + 1).Font.Bold = True i = Cells.Find("WoNo").Column For y = 2 To lastrow Cells(y, i) = Right(Cells(y, a), Len(Cells(y, a)) - 1) Next Cells(1, lastcol + 2) = "ActNo" For y = 2 To lastrow Cells(y, lastcol + 2) = Right(Cells(y, h), Len(Cells(y, h)) - 1) Next '***4) Match function iRow = Application.match(Cells(2, 10).Value, Range(Cells(2, 9), Cells(10, 9)), 0) On Error GoTo 0 If iRow 0 Then Cells(2, 11).Value = iRow End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=566620 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Type Mismatch - Match function
dim irow as Variant
iRow = Application.match(Cells(2, 10).Value, _ Range(Cells(2, 9),Cells(10, 9)), 0) if iserror(iRow) then msgbox "Was not found" exit sub End if -- Regards, Tom Ogilvy "T De Villiers" wrote: There is a slight problem with the asterixed section of the code (MATCH Function) if a value exists its all fine, but if there is an error I'm getting run-time error 13 - Type Mismatch Many Thanks Sub printtariq() On Error Resume Next Worksheets("Test").Select '1) Number the columns a = Cells.Find("WorkOrder").Column b = Cells.Find("CostC").Column c = Cells.Find("PaperSize").Column d = Cells.Find("PaperWeight").Column e = Cells.Find("NoBW").Column f = Cells.Find("NoColour").Column g = Cells.Find("printtype").Column h = Cells.Find("x").Column '2) Define last row and column lastrow = Range("A65500").End(xlUp).Row lastcol = Range("IV1").End(xlToLeft).Column '3) Strip letters from workorders and active workorders Cells(1, lastcol + 1) = "WoNo" Cells(1, lastcol + 1).Font.Bold = True i = Cells.Find("WoNo").Column For y = 2 To lastrow Cells(y, i) = Right(Cells(y, a), Len(Cells(y, a)) - 1) Next Cells(1, lastcol + 2) = "ActNo" For y = 2 To lastrow Cells(y, lastcol + 2) = Right(Cells(y, h), Len(Cells(y, h)) - 1) Next '***4) Match function iRow = Application.match(Cells(2, 10).Value, Range(Cells(2, 9), Cells(10, 9)), 0) On Error GoTo 0 If iRow 0 Then Cells(2, 11).Value = iRow End Sub -- T De Villiers ------------------------------------------------------------------------ T De Villiers's Profile: http://www.excelforum.com/member.php...o&userid=26479 View this thread: http://www.excelforum.com/showthread...hreadid=566620 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Type Mismatch: array or user defined type expected | Excel Programming | |||
Type mismatch in look up function | Excel Programming | |||
Help: Compile error: type mismatch: array or user defined type expected | Excel Programming | |||
Application.Match Type mismatch error | Excel Programming | |||
Type mismatch in VBA LinEst function if range too large | Excel Programming |