VBA Help to compare data and return result if match found

[Emmily]

Hi

I have a work task which I want to automate via VBA. I want the code to first clear the data in Col K "ControlSheet" and then do a lookup by looking at Col I and J in Sheet "ControlSheet" and compare this to the concatenated data in Col D in Sheet "HiportData" if match found then return Holding amount in Col K based on the holding shown in Col I "HiportData". If match not found then return 0 rather than #N/A. Please note there will be trailing spaces in the data, so the code will need to remove these first.

Real data will be around 1500 rows.

Excel Workbook
	I	J	K
1	Security Code	Pfolio Code	Hiport Holding
2	EQI0005	HRISII	15636.36
3	AMPSCF	CSFAUP
4	MFAFS004	N4S15X
5	MFAFS005	WSRAPP
ControlSheet

Excel Workbook
	D	I
1	UUTID	Holding
2	MFAFS004N4S15X	1,000.00
3	EQI0005HRISII	15,636.36
4	MFAFS005WSRAPP	98,784.00
HiportData

Thanks in advance

 

[hiker95]

Emmily,

Now we have all the bugs out.

After the macro:

Excel 2007
	I	J	K
1	Security Code	Pfolio Code	Hiport Holding
2	EQI0005	HRISII	15,636.36
3	AMPSCF	CSFAUP	0
4	MFAFS004	N4S15X	1,000.00
5	MFAFS005	WSRAPP	98,784.00
6
ControlSheet

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Sub GetHiportHolding_V3()
' hiker95, 11/15/2014, ME82838
Dim wc As Worksheet, wh As Worksheet
Dim c As Range, u As Range, h As String, lr As Long
Application.ScreenUpdating = False
Set wc = Sheets("ControlSheet")
Set wh = Sheets("HiportData")
With wc
  lr = .Cells(Rows.Count, "K").End(xlUp).Row
  If lr > 1 Then .Range("K2:K" & lr).ClearContents
  For Each c In .Range("I2", .Range("I" & Rows.Count).End(xlUp))
    h = Trim(c.Value) & Trim(c.Offset(, 1).Value)
    Set u = wh.Columns(4).Find(h, LookAt:=xlWhole)
    If u Is Nothing Then
      With c.Offset(, 2)
        .Value = 0
        .NumberFormat = "0"
        .IndentLevel = 0
      End With
    ElseIf Not u Is Nothing Then
      With c.Offset(, 2)
        .Value = wh.Cells(u.Row, 9).Value
        .NumberFormat = "#,##0.00"
        .IndentLevel = 0
      End With
      Set u = Nothing
    End If
  Next c
End With
wc.Activate
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the GetHiportHolding_V3 macro.

 

[Emmily]

Thanks Hiker this works

 

[hiker95]

Emmily,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Peter_SSs]

Certainly your choice about which code does the job and perhaps which one you might feel you could later best maintain if needed, but just noting that provided mine gives the correct values* it is about 10 times faster (as well as being considerably shorter).

* Mine adds all values in col I of HiportData where the col D value matches the two joined values from ControlSheet. So, if there are multiple matching rows, mine would give a different result to hikers code.
If there could be multiple matching rows and you only want the value from the first such matching row, I could suggest alternative code nearly as fast as the existing code.

At least you have choices.

 

[Emmily]

Hi Peter,

I have used your code due to being it much shorter and faster.

When you say multiple matching rows, do you mean duplicates?

 

[Peter_SSs]

When you say multiple matching rows, do you mean duplicates?

I mean if HiportData was like below, then a "MFAFS004N4S15X" row on ControlSheet would show 3,000 in col K. Hiker's code would show 1,000.

Excel Workbook
	D	E	F	G	H	I
1	UUTID					Holding
2	MFAFS004N4S15X					1,000.00
3	EQI0005HRISII					15,636.36
4	MFAFS005WSRAPP					98,784.00
5	MFAFS004N4S15X					2,000.00
HiportData

 

[Emmily]

No they would all be unique, no duplicates IDs, so all good.

 

[Peter_SSs]

No they would all be unique, no duplicates IDs, so all good.

OK, thanks for clarifying.