List of Names

rsernowski
rsernowski used Ask the Experts™
on
I had this question after viewing Index Match Vlook up for a master file.

Hi,
I have added some code to inform the user if the part does not exist on the master
For Each cell In rng
    If Application.CountIf(sws.Rows(4), cell.Value) > 0 Then
        col = Application.Match(cell.Value, sws.Rows(4), 0)
        r = Application.Match(cell.Offset(0, 2).Value, sws.Columns(col), 0)
       
        If col > 0 Then
            If Err.number = 13 Then
                cell.Offset(0, 3).Value = "Part not in master"
                Err.Clear
            Else
                 cell.Offset(0, 3).Value = sws.Cells(r, myCodeCol)
            End If
        End If
    End If
Next cell

i have been thinking about the first column and the issue getting all the invoices eventually to one sheet. The problem is that none of them put a column showing it is from them. I don't want the user to type in the company name becuase that would just open the door to problems So how about this.  when the user open the invoice spreadsheet, they will run a vba script , and it will ask who the company is:
ZED .. Maple.  etc.  the available companies  would be based upon ROW 4 in the master sheet in (sheet2). They choose the company and it is copied down in col A to the end of the data . I have run a few manual samples and it works good.  So my question is , how do i get that list to show up in VBA ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Oops sorry , forgot to attch the file

in SHeet 1 , Col A2 to end of data, I want to fill that column based upon the data in row 4 in the master tab.  So I guess the user would be presented with a list box  and choose the correct company. I can create drop downs user form based upon data in  a col. But I couldn't find anything to show data based upon row , or a name range?
Roy CoxGroup Finance Manager

Commented:
There is still no attachment

Author

Commented:
Funny , i thought I attached it, here it is
Index_match-VBA-invoices.xlsm
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Roy CoxGroup Finance Manager

Commented:
What is "MyCode"?

I can't tell what you re attempting from your code or the example. You mention invoices but where are they?

Explain clearly what you need and I may be able to help.

Author

Commented:
Hi

The existing macro matchs their part code to My code. the master shows the part numbers they use and MyCode is the part code I require.  (After the match I will pivot on group and qty and it will show me how many parts have been sold)

When I get monthly or qtrly invoice details from vendors , the details don't have a column showing who they are . So I need some way to add in company name. I suppose I could just match their part code to my part code without worrying about adding a company name? would that be easier to adjust the exiting code that way

If I use existing code , I need a way to add in company name so that the spelling is always the same as what is in row 4!  I  would  want the user to choose a company name based upon the names in the master sheet that are on row 4.  

I tried to  create a  list but list always works on column , not row.

the company name needs to be added prior to running the macro find vendor code, so that is why I thought I would just create two different macros.
Roy CoxGroup Finance Manager

Commented:
How can your code find "MyCode", it does not exist anywhere. If it is a variable then it should be declared and given a value. It should not be be surrounded by " in this case.

Author

Commented:
Ok

It seems to work as is, if I change it will it solve my problem?
Roy CoxGroup Finance Manager

Commented:
I can't see how it can work. How is it finding MyCode. Remove the error handler an see what happens - you should bnever use error handlers until coding is complete and tested.

Author

Commented:
the error handler is there to give the user a message in case their part code is not on the master. We are sort of off topic. I wanted to create a list based upon data in row 4
Roy CoxGroup Finance Manager

Commented:
Good luck. I am trying to understand your problem and help, but I cannot see how your code works the way it is written.

Author

Commented:
Nothing real solutions. please close

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial