Array Lookup on separate sheet

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
I'm trying to do a lookup on a separate sheet of the same workbook.

On sheet "Expenses" I want to lookup the data in column "E" for each row and match it to sheet "Accounting Column "A". When a match is found in on sheet "Accounting column A", return the value to sheet "Expenses Column H"

Then next same for all rows.

I am trying to learn this but its a slow process...this is the code I have thus far with no success. I am probably out to lunch on it. It is a hack job of some code I had someone assist me with for a bit different lookup.

Can someone assist me please?


Sub fillGL()

Dim lastRow As Long
Set lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

With Worksheets("Accounting")
datar = .range(.Cells(1, 1), .Cells(lastRow, 3)) ' pick all the data in columns A to C of Accounting sheet from row 1 to the last row
End With


With Worksheets("Expenses")
inarr = .range(.Cells(2, 1), .Cells(lastRow, 19)) ' pick up the values to look up "Expenses column E"
outarr = .range(.Cells(2, 1), .Cells(lastRow, 19)) ' define the output array
For i = 1 To UBound(inarr, 1) ' loop through each lookup value

For j = 1 To lastRow ' loop through each row of the Accounting data to find a match
If inarr(i, 3) = datar(j, 1) Then ' check for a match
outarr(i, 6) = datar(j, 3) ' copy data to output when matched

Exit For ' exit the inner loop because we have matched the look up

Next j

Next i


End With

End Sub
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
I need more details, you need be much more specific about which column were you writing data into and which column had a blank in it, and what you actually want to achieve.

I figured it out :)

Thanks for responding.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
Well I thought I solved it but I didn't.

On sheet "Expenses columns E, F, H, I & K" I have set to a data validation drop down list with invalid entry entry allowed.
I run the code from a call in the worksheet change event on sheet "Expenses"
If sheet Expenses column E finds a match in sheet Accounting column A, sheet Expenses columns F, H, I & K are populated with corresponding data from sheet Accounting.
Sheet Expenses column F is populated from Accounting Column F.
Sheet Expenses column H is populated from Accounting Column B.
Sheet Expenses column I is populated from Accounting Column C.
Sheet Expenses column K is populated from Accounting Column E.

The problem is if Accounting Column B, C, E or F is blank, I still want to be capable of manually entering data on Expenses F, H, I, K without the array overwriting it with blanks.
The way it is working right now is if Expenses Column E finds a match in Accounting column A it populates the corresponding columns in sheet Expenses F, H, I & K....but I cannot manually enter data in Expenses column F, H, I & K because the Array writes these columns back to blank when the code fires from a sheet change event on Expenses....because Accounting column B, C, E & F may actually have a blank.

I hope this makes sense.

VBA Code:

Sub fillExpensesLookups()

Dim LastRow As Long

With Worksheets("Accounting")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .range(.Cells(1, 1), .Cells(LastRow, 7)) ' pick all the data in columns A to C of Accounting sheet from row 1 to the last row
End With

With Worksheets("Expenses")
LastrowX = .Cells(Rows.Count, "A").End(xlUp).Row ' added this line because from your comment I think the last row on expense sheet is different to the accounting sheets
inarr = .range(.Cells(2, 1), .Cells(LastrowX, 19)) ' pick up the values to look up "Expenses column E"
outarr = .range(.Cells(2, 1), .Cells(LastrowX, 19)) ' define the output array . actually these two arrays are the same you only actually need one of them
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
For j = 1 To LastRow ' loop through each row of the Accounting data to find a match
If UCase(inarr(i, 5)) = UCase(datar(j, 1)) Then ' check for a match
outarr(i, 8) = datar(j, 2) ' copy data to output when matched
outarr(i, 9) = datar(j, 3)
outarr(i, 11) = datar(j, 5)
outarr(i, 6) = datar(j, 6)
Exit For ' exit the inner loop because we have matched the look up
End If
Next j
Next i
.range(.Cells(2, 1), .Cells(LastrowX, 19)) = outarr

End With
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
I notice you were trying to use CODE tags when posting your code , you nearly got it correct, you have got to make sure your code is BETWEEN the bit that says
[ C O D E = v b a ] and the
put your code in here
[ / C O D E ]
note I have put spaces in to prevent the website using it a formatting!!

try this:
VBA Code:
Sub fillExpensesLookups()

Dim LastRow As Long

With Worksheets("Accounting")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .Range(.Cells(1, 1), .Cells(LastRow, 7)) ' pick all the data in columns A to C of Accounting sheet from row 1 to the last row
End With

With Worksheets("Expenses")
LastrowX = .Cells(Rows.Count, "A").End(xlUp).Row ' added this line because from your comment I think the last row on expense sheet is different to the accounting sheets
inarr = .Range(.Cells(2, 1), .Cells(LastrowX, 19)) ' pick up the values to look up "Expenses column E"
outarr = .Range(.Cells(2, 1), .Cells(LastrowX, 19)) ' define the output array . actually these two arrays are the same you only actually need one of them
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
For j = 1 To LastRow ' loop through each row of the Accounting data to find a match
If UCase(inarr(i, 5)) = UCase(datar(j, 1)) Then ' check for a match
If datar(j, 2) <> "" Then
outarr(i, 8) = datar(j, 2) ' copy data to output when matched
End If
If datar(j, 3) <> "" Then
outarr(i, 9) = datar(j, 3)
End If
If datar(j, 5) <> "" Then
outarr(i, 11) = datar(j, 5)
End If
If datar(j, 6) <> "" Then
outarr(i, 6) = datar(j, 6)
End If
Exit For ' exit the inner loop because we have matched the look up
End If
Next j
Next i
.Range(.Cells(2, 1), .Cells(LastrowX, 19)) = outarr

End With
I nearly forgot to say , the really clear requiremnets makes it very easy well done!!
 
Last edited:

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
I notice you were trying to use CODE tags when posting your code , you nearly got it correct, you have got to make sure your code is BETWEEN the bit that says
[ C O D E = v b a ] and the
put your code in here
[ / C O D E ]
note I have put spaces in to prevent the website using it a formatting!!

try this:
VBA Code:
Sub fillExpensesLookups()

Dim LastRow As Long

With Worksheets("Accounting")
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
datar = .Range(.Cells(1, 1), .Cells(LastRow, 7)) ' pick all the data in columns A to C of Accounting sheet from row 1 to the last row
End With

With Worksheets("Expenses")
LastrowX = .Cells(Rows.Count, "A").End(xlUp).Row ' added this line because from your comment I think the last row on expense sheet is different to the accounting sheets
inarr = .Range(.Cells(2, 1), .Cells(LastrowX, 19)) ' pick up the values to look up "Expenses column E"
outarr = .Range(.Cells(2, 1), .Cells(LastrowX, 19)) ' define the output array . actually these two arrays are the same you only actually need one of them
For i = 1 To UBound(inarr, 1) ' loop through each lookup value
For j = 1 To LastRow ' loop through each row of the Accounting data to find a match
If UCase(inarr(i, 5)) = UCase(datar(j, 1)) Then ' check for a match
If datar(j, 2) <> "" Then
outarr(i, 8) = datar(j, 2) ' copy data to output when matched
End If
If datar(j, 3) <> "" Then
outarr(i, 9) = datar(j, 3)
End If
If datar(j, 5) <> "" Then
outarr(i, 11) = datar(j, 5)
End If
If datar(j, 6) <> "" Then
outarr(i, 6) = datar(j, 6)
End If
Exit For ' exit the inner loop because we have matched the look up
End If
Next j
Next i
.Range(.Cells(2, 1), .Cells(LastrowX, 19)) = outarr

End With
I nearly forgot to say , the really clear requiremnets makes it very easy well done!!
It works.

This only thing that would be nice is the ability to overwrite Expenses column F, if it was populate because of a match between Expenses Column E and Accounting Column A returned the value from Accounting Column F.

What this column is is A Vendor name that populates if a match is found between Expenses Column E and Accounting Column A. Sometimes we use an alternate Vendor in Expenses Column F.

Im not sure how this could work because the array is doing what its suppose to.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
You can't have it both ways!! However there is neat way round it. You could remove column F from this worksheet change event sub. Create a new sub which only writes into Column F and doesn't change anything else. trigger this sub from the before double click event with a test that the target is in column F So if you double click on a cell in column F it fills the Vendor name from the Accounting sheet column F.
This would allow you to write into it as well without over write when the change event happens
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
You can't have it both ways!! However there is neat way round it. You could remove column F from this worksheet change event sub. Create a new sub which only writes into Column F and doesn't change anything else. trigger this sub from the before double click event with a test that the target is in column F So if you double click on a cell in column F it fills the Vendor name from the Accounting sheet column F.
This would allow you to write into it as well without over write when the change event happens
I kinda thought that would be an issue. I’ll have to think about whether I can live with it or try your work around. Thanks for the help again, greatly appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,133,268
Messages
5,657,745
Members
418,411
Latest member
Excellency

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top