Help with Macro

[tarmand]

I have a a file in excel that contains a different sheet for each project and a sheet for a purchase order listing. I can't run a project report that lists vendor names, but I can run a project report that lists purchase orders and then run a purchase order lisiting. So, I need to have a macro run through my project report and match the purchase order on the report to the purchase order listing, grab that vendor name and insert it into a new row in the project report file. I would like to manually run the macro for each project sheet.

I need a macro that I can run to do the following:

Search in column "E" of my project report for a purchase order number (some will be empty)
Search in column "H" of the purchase order listing in the same workbook. It is named "Download" for the same purchase order number
Insert a column after column "E" in my project report titled "Vendor"
Insert the corresponding Vendor name from column "F" of the "Download" (purchase order) sheet in the new column.
If there is no purchase order, I need the Vendor column to be blank.

I'm really not good with creating macros. Here is what I have, but it's not working. I got this from another macro I use and tried to adjust it for my needs. Can anyone help? When I run this macro, it inserts the column, but doesn't put a vendor name in the inserted column.


Sub Vendor()

Columns("F").EntireColumn.Insert
Range("F1") = "Vendor"

Range([F4], [F65536].End(xlUp)).Offset(, 1) = _
"=VLOOKUP(F4,'Download'!$H$2:$F$2000,3,false)"
End Sub


Edited because I figured out part of my error, but it still doesn't work.

 

[tarmand]

I have tried just about everything that I can think of. Can anyone make a suggestion?

 

[HalfAce]

Hello tarmand,
There are some things I don't quite understand yet about your request so I'll try taking one
thing at a time.

Starting with the code posted.
This will work with the exception that we're inserting a new column F and then a formula to
perform a lookup on the value in F4. However there is no value being entered into F4 to do
the lookup on.
To start with, please confirm this works for you if you enter a value in F4 that can be found
in the first column of your lookup table in the Downloads sheet.

Sub Vendor()
Dim Rw As Long
Rw = Cells(Rows.Count, "C").End(xlUp).Row + 1

'Columns("F").EntireColumn.Insert
Range("F1") = "Vendor"
Cells(Rw, "F").Formula = _
"=VLOOKUP(F4,'Download'!$F$2:$H$2000,3,false)"
End Sub

This should work as expected, so once that's confirmed we can go from there.

 

[tarmand]

Hello tarmand,
There are some things I don't quite understand yet about your request so I'll try taking one
thing at a time.

Starting with the code posted.
This will work with the exception that we're inserting a new column F and then a formula to
perform a lookup on the value in F4. However there is no value being entered into F4 to do
the lookup on.

What I am trying to do is match the purchase order number in column "E" of my project sheet with the same number in "H" of my download (purchase order) sheet. When the purchase order number is located, I need to have the Vendor name in column "F" of my download (purchase order) sheet copied and inserted into the new column "F" in my project sheet.

For example:

"E4" in my project sheet says "45503963". I need to find "45503963" on the "download" sheet. That number happens to be on line "H13". "F13" lists the vendor name associated with that purchase order as "PSI Inc". I need the macro to find THE FIRST OCCURANCE (I left that out originally) of the purchase order "45503963" (which is "H13") and copy the vendor name, "PSI, Inc" (from "F13") and then insert it into a newly created vendor column "F" on my project page in cell "4' (F4). I need this done for every purchase order listed in column "E" of my project sheet.

Does that clear it up more?

The below macro did nothing that I could see when I ran it in my sheet.


To start with, please confirm this works for you if you enter a value in F4 that can be found
in the first column of your lookup table in the Downloads sheet.

Sub Vendor()
Dim Rw As Long
Rw = Cells(Rows.Count, "C").End(xlUp).Row + 1

'Columns("F").EntireColumn.Insert
Range("F1") = "Vendor"
Cells(Rw, "F").Formula = _
"=VLOOKUP(F4,'Download'!$F$2:$H$2000,3,false)"
End Sub

This should work as expected, so once that's confirmed we can go from there.

 

[HalfAce]

Yes, that does help some.
What are all the actual sheet names involved?
(ie, "Download"? - Purchase Order"? - "Project"?)
(This shouldn't be hard to get going.)

 

[tarmand]

Yes, that does help some.
What are all the actual sheet names involved?
(ie, "Download"? - Purchase Order"? - "Project"?)
(This shouldn't be hard to get going.)

They will be named the Project name - ex "NS75113" is one project name, but "TAS44SA" is another project name. They aren't really similar. There will be about 100 different sheets . Can't I just create the generic macro and then manually run it each time I add a project sheet? "Download" is the sheet name that I will always search for the purchase order match on. Also, the format of each project sheet will always stay the same.

Thanks,
Tammie

 

[HalfAce]

Sure. We'll just make it to run on the active sheet and search for a match in the sheet named "Download".

Might take a bit (not too long I hope). Got quite a bit going on here today.

 

[tarmand]

Sure. We'll just make it to run on the active sheet and search for a match in the sheet named "Download".

Might take a bit (not too long I hope). Got quite a bit going on here today.

I'm leaving in a few - gotta get to daycare in time. I appreciate you help. I will check back in the morning.

Thanks,
Tammie

 

[HalfAce]

Hi Tammie,
Had to make a few assumptions. (Well one assumption and a question.)
The question first.
- Am I correct in thinking you want all these new column Fs added to the same (active) sheet? I can see some real issues if there are very many PO numbers. (We only have at present 256 columns to work with. Is this going to be a problem?)

And the code assumes you want to start searching through column H of the Download sheet starting in row 1, as well as searching for every value in column E of the active sheet starting in row 1. if this isn't right it can be changed easily enough to whatever row you want.

Give this a try and see how close it is (if at all) to what you're after.

Sub Demo()
Dim DownloadRng As Range, _
    ProjectRng As Range, _
    ProjectNo As Range, _
    ProjectFound As Range, _
    Rw As Long
    
With Sheets("Download")
  Set DownloadRng = .Range("H1:H" & .Cells(Rows.Count, "H").End(xlUp).Row)
End With
Set ProjectRng = Range("E1:E" & Cells(Rows.Count, "E").End(xlUp).Row)
Rw = Cells(Rows.Count, "C").End(xlUp).Row + 1

For Each ProjectNo In ProjectRng
  Set ProjectFound = DownloadRng.Find(ProjectNo, lookat:=xlWhole)
  If Not ProjectFound Is Nothing Then
    Columns("F").EntireColumn.Insert
    Range("F1").Value = "Vendor"
    Range("F4").Value = ProjectFound.Offset(, -2).Value
    Cells(Rw, "F").Formula = "=VLOOKUP(F4,'Download'!$F$2:$H$2000,3,false)"
  Else
    MsgBox ("The project number - " & ProjectNo & " - was not found on the Downloads sheet."), , "Project not found"
  End If
Next

End Sub

 

[tarmand]

Oaky, I think we are almost there. I don't need but one inserted column per page. I need column "F" to be named vendor and then I need the vendor name for each p.o. to be listed in that column. So, shouldn't we insert a column and then have the macro do the search for the purchase order number/vendor name and then insert the findings into that created column?

Make sense?

Tammie