How to use Application.match?

Refresh

April 2019

Views

9 time

0

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

1 answers

0

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