Hi everyone,
I am having trouble with a match function. I don't know what I am am doing wrong. If I use the match function and manually specify the matchval and matchrng I get the answer that I am looking for, yet when I use VBA I get an error (Runtime error 1004, application-defined or object defined error).
Initially the different variables weren't defined and that also produced the above mentioned error. Will somebody please help me with this issue?
In order to highlight where I used the variables (matchval and matchrng) is used bold letters.
I thank you in advance for your time!
I am having trouble with a match function. I don't know what I am am doing wrong. If I use the match function and manually specify the matchval and matchrng I get the answer that I am looking for, yet when I use VBA I get an error (Runtime error 1004, application-defined or object defined error).
Initially the different variables weren't defined and that also produced the above mentioned error. Will somebody please help me with this issue?
In order to highlight where I used the variables (matchval and matchrng) is used bold letters.
Code:
[B]Dim matchrng As Range[/B]
'other variables like i, m y and x are also defined
Sheets("Information").Select
Columns("B:B").Select 'Copy all units over to summary sheet
Selection.Copy
Sheets("Summary").Select
Range("N1").Select
ActiveSheet.Paste
Sheets("Information").Select
Columns("D:D").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Summary").Select
Range("O1").Select
ActiveSheet.Paste
Range("O1").Select
Selection.End(xlDown).Select
m = ActiveCell.Row
'some code
Range("T1").Select
ActiveCell.Formula = "Module"
Range("T2").Select
ActiveCell.FormulaR1C1 = "=MID(RC[-5],FIND(RC[-1],RC[-5],1)+LEN(RC[-1]),3)"
If m > 2 Then
Range("T2").Select
Selection.AutoFill Destination:=Range("T2:T" & m), Type:=xlFillDefault
End If
Range("N1").Select
Selection.End(xlDown).Select
i = ActiveCell.Row
[B] Range("S2").Select[/B]
[B] matchvalue = ActiveCell.Text[/B]
For k = 2 To i
Range("N" & k).Select
sht = (ActiveCell.Value & " Sheet")
If sht = "Jig Sheet" Then
Sheets(sht).Select
Range("P1").Select
Selection.End(xlToRight).Select
y = ActiveCell.Column
Cells(1, y + 3).Select
fractions = ActiveCell.Value
[B] Set matchrng = Range(Cells(2, y + 11), Cells(4, y + 11))[/B]
End If
'Additional if statements removed to truncate the code. Code is similar to this if statement
Sheets("Summary").Select
Range("U" & k).Select
[B] ActiveCell.FormulaR1C1 = Application.WorksheetFunction.Match(matchvalue, Sheets(sht).Range(matchrng), 0) 'Error at this line[/B]
lineval = ActiveCell.Value
Sheets(sht).Select
For x = 2 To 4 ' since there can be a max of 3 fractions in the spesified range, rows 2 to 4, x should only run between them
If lineval = 2 Then
Cells(lineval, y + 12).Select
Selection.End(xlToRight).Select
col = ActiveCell.Column - (y + 12)
If col > 100 Then
Cells(lineval, y + 12).Select
Selection.Copy
Else
Range(Cells(lineval, y + 12), Cells(k, y + 12 + col)).Select
Selection.Copy
End If
End If
Next x
Sheets("Summary").Select
Range("U" & k).Select 'it is ok to paste over cell that contains match function
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("S" & k).Select
ActiveCell.Offset(1, -1).Activate
matchvalue = ActiveCell.Value
Next k
I thank you in advance for your time!