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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
that is undoubtedly my code, I have have pointed out before you must be careful about defining your requirements , there are four columns needed to define a "lookup" the two columns to do the match and the source of the data and the destination of the data. You have only defined three of the colums, i.e match Expense/E to Acounting/A destination Expense/H what column is the source on Accounting ??
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
looking at your code the only probelm is you haven't written the output array back tothe workhseet:
just add this line before the last END WITH
VBA Code:
.Range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
that is undoubtedly my code, I have have pointed out before you must be careful about defining your requirements , there are four columns needed to define a "lookup" the two columns to do the match and the source of the data and the destination of the data. You have only defined three of the colums, i.e match Expense/E to Acounting/A destination Expense/H what column is the source on Accounting ??
I see I missed that. It’s in column C
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

looking at your code the only probelm is you haven't written the output array back tothe workhseet:
just add this line before the last END WITH
VBA Code:
.Range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr
Im getting a compile error: invalid or unqualified reference on this line>>>Set lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

its not liking .cells
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
you needto swap these two lines around:
VBA Code:
Set lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

With Worksheets("Accounting")
The reason is the dot means that the subsequent reference i.e Cells , refers to the worksheet defined in the With statement.. becasue the Set lastrow was before the with , you get an error.
Sorry I didn't spot that
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

you needto swap these two lines around:
VBA Code:
Set lastRow = .Cells(Rows.Count, "A").End(xlUp).Row

With Worksheets("Accounting")
The reason is the dot means that the subsequent reference i.e Cells , refers to the worksheet defined in the With statement.. becasue the Set lastrow was before the with , you get an error.
Sorry I didn't spot that
That is giving a compile error: object error

doesn’t like lastRow
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
you don't need the "set". I also had to add and endif try this code:
you only need to use set when the object is somehting complicate like a range, I avoid using set as much as possible by loading ranges into variant arrays , much faster as shown here
VBA Code:
Sub fillGL()

Dim lastRow As Long

With Worksheets("Accounting")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
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
End If
Next j

Next i


End With

End Sub
By the way when posting code please use the code tags so that it is highlighted within the post , just click the cloud symbol with vba under it
 

bhalbach

Board Regular
Joined
Mar 15, 2018
Messages
151
Office Version
  1. 2016
Platform
  1. Windows
I have obviously explained something wrong again. I am obviously misunderstanding this...I thought I was starting to understand.

Sorry, this must be frustrating to assist someone when they are not getting it.

When I use your code with the addition of>>>>.Range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr 'for writing the data back to sheet Expenses column H
...it adds a bunch of rows to sheet expenses. I end up with 45 rows including headers, the same amount of rows that I have on sheet Accounting range A:C & last row.

My data is on sheet Accounting starting at A2:C & last row...I have headers on row 1 so the data starts in row 2
My Expenses sheet Range is A:S & last row...I have headers so the first row is 2

Lookup value in Expenses column E
Look At Accounting column A for a match
If a match Take value from Accounting C
Put that value in Expenses H
If No match leave Expenses H blank
Loop through all rows of Expenses column E and repeat

The values in Expenses column E are text
The values in Accounting A are text
The values in Accounting C are Text
The values in Expenses H will be the Text value from Accounting C

Can't be case sensitive for the lookup and match.

I added this line that you gave me to the code>>>.Range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr 'to write the output to Expenses column H

This the the code...

Sub fillGL()

Dim lastRow As Long

With Worksheets("Accounting")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
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 to Expenses column E

Exit For ' exit the inner loop because we have matched the look up
End If
Next j

Next i

.range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr ' write data to Expenses column H

End With

End Sub
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,860
Office Version
  1. 2010
Platform
  1. Windows
try this

VBA Code:
Sub fillGL()

Dim lastRow As Long

With Worksheets("Accounting")
lastRow = .Cells(Rows.Count, "A").End(xlUp).Row
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")
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, 3)) = UCASE(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
End If
Next j

Next i


End With

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,133,271
Messages
5,657,760
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