CODEWITHSUNDEEP
excelvba
Manos Alexopoulos
Manos Alexopoulos

Reputation: 5

How to use Application.match?

I am new to Excel VBA.

I have these random columns:

Range("Y1").Value = "LITIGATE_PERSON_ADDRESS"
Range("Z1").Value = "LITIGATE_PERSON_CITY"
Range("AA1").Value = "LITIGATE_PERSON_TK"
Range("AB1").Value = "LITIGATE_PERSON_ADDRESS_TYPE"

Here's the part of code that is being used for these columns.


  Worksheets("MAIN_CONTROL").Cells(i, 25).Value = sourceADR

 sourceADR = Replace(sourceADR, "Attica", "")



 '...................................................................
  sourceADR = Replace(sourceADR, "-", " ")
  sourceADR = Replace(sourceADR, "  ", " ")
  sourceADR = Trim(sourceADR)

  auxC = sourceADR

Worksheets("MAIN_CONTROL").Cells(i, 26).Value = sourceADR
'..............................
  If (Len(sourceADR) < 1) Then GoTo aseAddr
'..............
 mainAddress = Split(sourceADR)

  addrAA = ""

Worksheets("MAIN_CONTROL").Cells(i, 24).Value = Str(UBound(mainAddress)) & "@@" & Str(LBound(mainAddress))


  For jA = UBound(mainAddress) To LBound(mainAddress) Step -1
 '......................................................


If (regex.Test(Trim(mainAddress(jA)))) Then

auxC = Replace(auxC, Trim(mainAddress(jA)), "")

 destws.Range("BT" & i).Value = Trim(mainAddress(jA))
 destws.Range("AA" & i).Value = Trim(mainAddress(jA))
 destws.Range("Z" & i).Value = addrAA

auxC = Trim(auxC)
 destws.Range("Y" & i).Value = auxC
'--------------------------------------------------------

'-------------------------------------------------------

GoTo aseAddr


 End If

auxC = Replace(auxC, Trim(mainAddress(jA)), "")
addrAA = mainAddress(jA) & " " & addrAA








'

 '.....................................................
 Next jA
 '.........................................................
  'destws.Range("Y" & i).Value = addrAA
'.....................
aseAddr:
'.................................

My problem is that these columns may change order. I was suggested to use application.match so that my code may follow but i don't know how to put it inside my code.

Can anyone help? Thanks in advance

Upvotes: 0

Views: 3855

Answers (1)

Stavros Jon
Stavros Jon

Reputation: 1697

Welcome to SO.

In general, the way you can use an excel function in VBA is the following:

application.WorksheetFunction.Match() 'where match() can be replaced by one of the available worksheet functions.

The function's arguments work pretty much the same way as they do when you use the formula in your worksheet.

The Match functionality is explained thoroughly here

So for example let's say you have an array like the following one in cells A1:A13

enter image description here

And you want to find the location of "Friday". You would do it like so:

Debug.Print Application.WorksheetFunction.Match("Friday", sht.Range("A1:A13"), 0)

And you would get 5 as a result.

That should pretty much cover the "How to use Application.Match()".

Upvotes: 2

Related Questions