PDA

View Full Version : Vlookup or Index/Match code to find the value



datvu
10-14-2016, 01:43 PM
Hi, guys

I have these two sheets:

Transactions
17325

Table

17324



I am struggling to use Vlookup or Index/Match to find the value in ACCT column on Transactions sheet based on the "Table" sheet and Descriptions column on "Transactions" sheet.
It would be great if anyone can help me.

Thank you.

mana
10-14-2016, 05:47 PM
Option Explicit

Sub test()
Dim v
Dim r As Range
Dim i As Long
Dim c As Range
Dim f As String

v = Sheets("Table").UsedRange.Columns("A:B").Value
Set r = Sheets("Transactions").UsedRange.Columns("H")
r.Offset(1, 1).ClearContents

For i = 2 To UBound(v)
Set c = r.Find(What:=v(i, 1), LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)
If Not c Is Nothing Then
f = c.Address
Do
c.Offset(, 1).Value = v(i, 2)
Set c = r.FindNext(c)
Loop Until c.Address = f
End If
Next

End Sub

mana
10-14-2016, 06:39 PM
Option Explicit

Sub test2()
Dim rr As Range, r As Range
Dim cc As Range, c As Range
Dim i As Long

Set rr = Sheets("Transactions").UsedRange.Columns("H")
Set rr = Intersect(rr, rr.Offset(1))
rr.Offset(, 1).ClearContents

Set cc = Sheets("Table").UsedRange.Columns("A")
Set cc = Intersect(cc, cc.Offset(1))

For Each r In rr
For Each c In cc
If r.Value Like "*" & c.Value & "*" Then
r.Offset(, 1).Value = c.Offset(, 1).Value
End If
Next
Next

End Sub

mana
10-14-2016, 07:06 PM
I was taught in japanese QA site recently

1)define name
name;
List_ACCT
refers to :
=OFFSET('Table'!$A$1,0,0,COUNTA('('Table'!'!$A:$A),1)

2)formula of I2
=IFERROR(LOOKUP(0,0/FIND(List_ACCT,H2),OFFSET(List_ACCT,,1)),"")


3)Fill it down

datvu
10-17-2016, 08:31 AM
wow, thank you so much. It worked. I am fairly new to VBA so there are a lot of things for me to learn. Really appreciate your help.

datvu
10-17-2016, 09:06 AM
Would you mind to do me another small favor. So this is what I am doing right now and I don't know how to integrate your code into mine. If you could help me with this, it would be great.

I attached the file below. If you could look over the file, it will explain my intention easier. Ultimately, I want the end result from your code to be in here QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol))




Sub TansActions2QuickBook()


'Transactions columns
Const SettleDateCol As String = "C"
Const TypeCol As String = "G"
Const DescCol As String = "H"
Const USDAmtCol As String = "U"

'QuickBook Columns
Const DateCol As String = "D"
Const MemoCol As String = "I"
Const AcctCol As String = "E"
Const AmmtCol As String = "G"
Const TrnstypeCol As String = "C"
Const TrnsCol As String = "A"
Dim TransSht As Worksheet
Dim TransRow As Long

Dim QBSht As Worksheet
Dim QBRow As Long

Set TransSht = Sheets("Transactions")
Set QBSht = Sheets("QuickBook")

QBRow = QBSht.Cells(Rows.Count, DateCol).End(xlUp).Row + 2

With TransSht
For TransRow = 2 To .Cells(Rows.Count, SettleDateCol).End(xlUp).Row
QBSht.Cells(QBRow, TrnsCol).Value = "TRNS"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, MemoCol) = .Cells(TransRow, TypeCol)
QBSht.Cells(QBRow, AcctCol) = GetAcct(.Cells(TransRow, DescCol)) <==== Where I want to put the result from your code into
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol) * -1

QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "SPL"
QBSht.Cells(QBRow, TrnstypeCol).Value = "GENERAL JOURNAL"
QBSht.Cells(QBRow, DateCol) = .Cells(TransRow, SettleDateCol).Text
QBSht.Cells(QBRow, AcctCol) = GetAcct("JPMorganCash")
QBSht.Cells(QBRow, AmmtCol) = .Cells(TransRow, USDAmtCol)

QBRow = QBRow + 1
QBSht.Cells(QBRow, TrnsCol).Value = "ENDTRNS"

QBRow = QBRow + 1
Next TransRow
End With

End Sub

Private Function GetAcct(Desc As String) As String
Dim WsF As WorksheetFunction
Set WsF = Application.WorksheetFunction

Dim VLTable As Range
Set VLTable = Sheets("Description Name").Range("A:B")

GetAcct = WsF.VLookup(Desc, VLTable, 2, False)

End Function