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.

 

[HalfAce]

Yes, that actually makes more sense.
Only question I'm left with is this. Your request was to put the vendor into row 4 of column F and the lookup formula in column F of the row below the end of column C.
Obviously this will overwrite each vendor name and lookup formula with the next one for each po number found, leaving only the data for the last found po displayed.
How do you want to handle that?

Also, it seems to me the lookup formula is just returning the value in column H of the download sheet that’s being matched from column E of the active sheet. Is that what you’re looking to do?

Just as a suggestion, how about if we match each number from activesheet column E with Download sheet column H, and where there's a match, simply list the vendor name in column F next to the matched number on the active sheet? Is that anythng like what you're looking for, or do you need the matched number to be in column F down below the end of column C's data for some reason?

 

[tarmand]

Just as a suggestion, how about if we match each number from activesheet column E with Download sheet column H, and where there's a match, simply list the vendor name in column F next to the matched number on the active sheet? Is that anythng like what you're looking for, or do you need the matched number to be in column F down below the end of column C's data for some reason?

That's exactly what I wanted to do. I didn't want to list the results in row 4 only, I want the vendor name to show in the same row as the purchase order number, but in the new column "F".

Thanks,
Tammie

 

[HalfAce]

Hi Tammie,
Try replacing the existing code with this & see what you think.

Sub Demo()
Dim DownloadRng As Range, _
    ProjectRng As Range, _
    ProjectNo As Range, _
    ProjectFound As Range
    
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)
Columns("F").EntireColumn.Insert
Range("F1").Value = "Vendor"

For Each ProjectNo In ProjectRng
  Set ProjectFound = DownloadRng.Find(ProjectNo, lookat:=xlWhole)
  If Not ProjectFound Is Nothing Then
    With Cells(ProjectNo.Row, "F")
      .Value = ProjectFound.Offset(, -2).Value
    End With
  Else
    MsgBox ("The project number - " & ProjectNo & " - was not found on the Downloads sheet."), , "Project not found"
  End If
Next

End Sub

 

[tarmand]

That's great. I'd like to make one change, but I am so afraid that I will mess the whole thing up. Can you take out the prompt box stating that no vendor name was found and just have it leave the vendor name blank for those purchase orders that have no match?

Thanks so much! You are a life and timesaver!

Tammie

 

[HalfAce]

You're very welcome.
Sorry it's taking so long to get back to you. You'd never believe what's been going on
here. (18.5 (+) inches of rain in 2 days really plays havoc on an electric company.)

Anyway, that change is easy. Just need to delete a few lines.

Sub Demo()
Dim DownloadRng As Range, _
    ProjectRng As Range, _
    ProjectNo As Range, _
    ProjectFound As Range
    
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)
Columns("F").EntireColumn.Insert
Range("F1").Value = "Vendor"

For Each ProjectNo In ProjectRng
  Set ProjectFound = DownloadRng.Find(ProjectNo, lookat:=xlWhole)
  If Not ProjectFound Is Nothing Then _
    Cells(ProjectNo.Row, "F").Value = ProjectFound.Offset(, -2).Value
Next

End Sub

How's that grab ya?

 

[tarmand]

That's perfect!!!!!!!

So sorry about all the rain. If I ruled the world I'd only let it rain at night while people are sleeping.

 

[HalfAce]

You get my vote!