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

 

[bhalbach]

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.

 

[bhalbach]

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.

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]

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:

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!!

 

[bhalbach]

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:

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]

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]

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.