How to Partial match

[Abhijeet]

Hi

I want to know how to partial match in VBA

 

[Debaser]

Fairly vague question. You can use Instr, Find or Match with a wildcard. Depends on the situation.

 

[Abhijeet]

how to write code if i want to match 2 things then

 

[Debaser]

Still a bit vague I'm afraid. Are you trying to match two strings against each other? If so, you can see if one appears somewhere in the other using Instr:

If instr(1, "text to search", "text to look for", vbTextCompare) <> 0 Then
msgbox "Text was found"
End If

 

[Abhijeet]

both think i want to match trust & batch name

 

[Debaser]

You can use Instr for that.

 

[Abhijeet]

Data in different sheets so can u pls give code for this

 

[Debaser]

Afraid not - you still haven't clearly explained what exactly you are trying to achieve.

 

[Abhijeet]

Sub ProcessData()
Dim wksSource As Worksheet, wksDestn As Worksheet
Dim lngSrcLastRow As Long, lngDstLastRow As Long, i As Long, j As Long

'\\ Set necessary references
Set wksSource = Worksheets("Allocation Data")
Set wksDestn = Worksheets("Tally")
lngSrcLastRow = wksSource.Range("A" & Rows.Count).End(xlUp).Row
lngDstLastRow = wksDestn.Range("A" & Rows.Count).End(xlUp).Row

Application.DisplayAlerts = False
Application.ScreenUpdating = False

'\\Loop through ranges on both sheets
For i = 2 To lngSrcLastRow
    For j = 2 To lngDstLastRow
        If wksDestn.Range("A" & j).Value = wksSource.Range("A" & i).Value And _
        InStr(wksDestn.Range("B" & j).Value, wksSource.Range("B" & i).Value) > 0 Then
            wksDestn.Range("P" & j).Value = wksSource.Range("C" & i).Value
            wksDestn.Range("Q" & j).Value = wksSource.Range("D" & i).Value
            wksDestn.Range("R" & j).Value = wksSource.Range("E" & i).Value
            wksDestn.Range("S" & j).Value = wksSource.Range("F" & i).Value
            wksDestn.Range("T" & j).Value = wksSource.Range("G" & i).Value
            wksDestn.Range("U" & j).Value = wksSource.Range("H" & i).Value
            wksDestn.Range("V" & j).Value = wksSource.Range("I" & i).Value
            '\\ Remove comment mark from below line if you expect only ONE match
          'Exit for
      End If
    Next j
Next i

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

Mod Edit: Code tags added!

 

[Abhijeet]

I got solution can u pls tell me how to use Countifs function in vba for this formula
=COUNTIFS('Process Data'!$A:$A,Tally!$A3,'Process Data'!$C:$C,Tally!$B3,'Process Data'!$H:$H,Tally!H$2)

 

[shrivallabha]

Abhijeet,

You should provide cross-reference to the post so that answering party doesn't have to check / find the same things.

http://chandoo.org/forum/threads/tally-data-with-multiple-criteria.28070/

 

[Abhijeet]

I post this before you provide ans