Array Lookup on separate sheet

[bhalbach]

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

 

[offthelip]

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]

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

.Range(.Cells(2, 1), .Cells(lastRow, 19)) = outarr

 

[bhalbach]

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]

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

.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]

you needto swap these two lines around:

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]

you needto swap these two lines around:

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]

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

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]

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]

try this

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