List of Names
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.Ro
col = Application.Match(cell.Val
r = Application.Match(cell.Off
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 ?
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.Ro
col = Application.Match(cell.Val
r = Application.Match(cell.Off
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 ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Roy Cox
There is still no attachment
ASKER
Funny , i thought I attached it, here it is
Index_match-VBA-invoices.xlsm
Index_match-VBA-invoices.xlsm
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.
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.
ASKER
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.
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.
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.
ASKER
Ok
It seems to work as is, if I change it will it solve my problem?
It seems to work as is, if I change it will it solve my problem?
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.
ASKER
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
Good luck. I am trying to understand your problem and help, but I cannot see how your code works the way it is written.
ASKER
Nothing real solutions. please close