VBA MATCH function issue

[dancingcab]

Hi,

I'm fairly new to VBA so please forgive my lack of knowledge!

I'm trying to use the Match function but get a run-time error 1004. My code is as follows:

Sub CAAnalysis()


Worksheets(1).Activate


Start1 = Application.WorksheetFunction.Match(0.05, "$A:$A", 1)


Debug.Print Start1


End Sub

I know that the value 0.05 exists in column A. However, the actual value is 0.0499999998737621. I noticed that the Match function says As Double when I click the quick info button. Is the issue that the value is more than 15 decimal places?

Thanks!

 

[steve the fish]

This should work for you

Sub CAAnalysis()

Worksheets(1).Activate

Start1 = [MATCH(0.05,ROUND(A:A,10000),0)]
Debug.Print Start1

End Sub

 

[dancingcab]

Hi,

If I use:

Start1 = [MATCH(0.05,ROUND(A:A,10000),0)]

I get a Type Mismatch error. I presume this is because I have defined start1 as an integer (which it should be as I'm hoping it will give me the the row number for that value). If I take out the [Dim Start1 As Integer] line I get an error 2042 (#N/A).

Full code:
Dim Start1

Sub CAAnalysis()


Worksheets(1).Activate


Start1 = [MATCH(0.05,ROUND(A:A,10000),0)]


Debug.Print Start1




End Sub

 

[steve the fish]

Sorry this better

Sub CAAnalysis()
Dim Start1 as integer
 Worksheets(1).Activate

Start1 = [MATCH(0.05,ROUND(A:A,5),0)]

Debug.Print Start1

End Sub

 

[dancingcab]

Thank you! This works

The square brackets are new to me - can you point me in the direction of any tutorials which help explain what this is doing?

Thanks!

 

[steve the fish]

Try this:

Evaluate - Most Powerful Command in VBA?