Solved

Module Required in EXCEL that searches for a variable and applies a set price if it is True

Posted on 2013-05-31
22
263 Views
Last Modified: 2013-06-13
I recently requested a code on an excel spreadsheet that I developed, which searched for a parameter in a cell and if those parameters met, it would add a price to it.  This worked out so well, that to make my invoicing even easier, I would like to add an additional code to the spreadsheet.

In this new coding, using the worksheet "Zones + Tolls" and data in column J, K, L and M what I would like it do is the following:
When I enter a specific text in the cells of W/S Invoice Unity Chauffeur in Column D ...

1.

It searches the data in worksheet "Zones + Tolls" and if a specific text of a name matches (regardless of capitals or not, but it must match the name), it goes to step 2 ...  If the name does not match it goes to step 4.

2.

It then compares the entry in Column E of W/S Invoice Unity Chauffeur with the P/U Zone (column K of W/S "Zones + Tolls").  If this matches ... it goes to step 3 ... If it does not match it writes $0.00 in column M of W/S Invoice Unity Chauffeur.  This task then ends and it waits for the next row.

3.

It then compares the entry in Column F of W/S Invoice Unity Chauffeur with the D/O Zone (column L of W/S "Zones + Tolls").  If this matches ... it goes to step 5 ... If it does not match it writes $0.00 in column M of W/S Invoice Unity Chauffeur.  This task then ends and it waits for the next row.

4.

It compares the entry in Column E of W/S Invoice Unity Chauffeur with the P/U Zone (column K of W/S "Zones + Tolls").  If this matches ... it goes to step 3 ...  If it does not match it writes $0.00 in column M of W/S Invoice Unity Chauffeur.  This task then ends and it waits for the next row.

5.

It then displays the price in Column M from W/S "Zones + Tolls" on the cell in column M of W/S Invoice Unity Chauffeur of the same row of the client's name.  This task is then finished and it begins again in the next row.
Please note: that in the cells of column D in W/S Invoice Unity Chauffeur there may additional wording added.  So when the code is working, it should only be searching for a matching word that would be in Column J of W/S "Zones + Tolls".

I hope this helps.  If you require clarity on what I require, please don't hesitate to let me know.  The first coding has worked so well, this will make my life so much easier.

I have attached the excel file as well.
01-Unity-Invoice-Template-blank-.xls
0
Comment
Question by:Worldsplayground
  • 11
  • 11
22 Comments
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39212306
ok. there is already a function in Column M called JobNo so we will need to extend that.

also, there is no data on the Zones page for areas yet. will it be sorted or just assume any order and search list ?
0
 

Author Comment

by:Worldsplayground
ID: 39212320
There is no data in the zones page at this stage. The data will be assorted by client name, then P/U and finally D/O.

I am aware there is a code for column M. This is the code that worked so well, that I want to add the new code.

This new code is only to work if Column C in the W/S Invoice Unity Chauffeur does not contain the letter e, which activates the function JobNo.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39212355
trial this.  the blank client cells need to be at the bottom, so sort descending.
02-Unity-Invoice-Template-blank.xlsm
0
 

Author Comment

by:Worldsplayground
ID: 39212369
Hi robberbaron,
The first thing I noticed is that you saved the excel spreadsheet with macro extension.  Unfortunately, this would not work for me, as some of my clients and employees, do not have the new version of EXCEL.  So we need to keep the file saved with the extension .xls for compatibility reasons.

I have tested out the template and there are few things I have noticed ...
1) The code does not seem to work.  I have added some entries into the Zones W/S and you can see from the attachment it doesn't show the amount listed.

2) The error #VALUE! is displayed in the rows where the cells have no entries.  I would prefer that it remained as it was before, where all the cells are blank if there is no calculation or entry.

I have attached the tested template for you to see what I mean.
02-Unity-Invoice-Template-blank-.xlsm
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39212422
updated. not sure if the result should be returned as text (currently) or as a number for use in the next formula.
01-Unity-Invoice-Template-03.xls
0
 

Author Comment

by:Worldsplayground
ID: 39212483
This is great. Thanks.

The answer to your question is the result in Column M in W/S Invoice should be returned as a number (without the $ sign) so that each row is calculated accordingly with the other formulas listed in each cell/row.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39212519
changed to return decimal value when suitable. uses variant to handle empty data.
01-Unity-Invoice-Template-04.xls
0
 

Author Comment

by:Worldsplayground
ID: 39212648
Thank you so very much robberbaron for setting this up for me.  Your experience and kindness to work for free is admirable and I greatly appreciate it.

I have tested out almost all the possible things that may happen and everything seems to work perfectly except for the row I have highlighted in RED.

Basically, if the content in cell D of W/S Invoice has other content in there, the function won't recognize it.  Is there a way to fix this.  What I would like the function to do, is simply search for a match in that cell.  If it finds anything that matches that keyword (client's name) then function works.  If it does not, then it doesn't work kind of thing.

Often in cell D, notes will written, about waiting time or issues or surcharges that may have arisen with a particular client, so I am hoping that there would be a way that it looks for that particular text, within the cell rather than have an exact matching cell.

Also, I have changed the formatting in column M, so that it does not have the $ sign showing.  I only want column M to have numbers with 2 decimals places, which I have formatted now.  I have done the same thing for the W/S Zones + Tolls

I have attached the saved copy of the template to show you want I mean.  the issue is highlighted in RED.
01-Unity-Invoice-Template-04.xls
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39212830
how would such a partial match work though ?
could search for same  starting text i guess.


currently it is a simple
        ElseIf newclient = clientzone Then

Open in new window


this could be expanded but I would be concerns it would lead to some unexpected matches though....
steve
steven
stevens
port stevens
john elliot
elliot goblet
0
 

Author Comment

by:Worldsplayground
ID: 39213476
I see what you are saying.
How about, I am not sure if this is possible, but when we enter the job details, we do it the following way ...
The first line in the cell is the client's name.
The second line after pressing (ALT + Return) is the job description

Is it possible for this to work?
0
 

Author Comment

by:Worldsplayground
ID: 39213480
Actually thinking about it in detail, I have come up with a better solution.

If such a discrepancy comes up, as you have indicated, can we have the following happen:

1) A pop up window opens up.
2) In the pop up window, it displays potential matches from the database in W/S Zones + Tolls.
3) I click on or select the correct match by clicking on a radio button next to the correct match or tick a checkbox and then PRESS OKAY.
4) Once I select the correct match, it then proceeds as normal.

This of course will still need to allow me to enter the Job description or other details in the cell.

I like the sound of this option better than the once I mentioned above.
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39213646
i guess it would be possible but ive not done much with forms as they always seemed unwieldy.

would also be quite slow (comparitivley anyway) as would have to read all the clientnames into an array for the check.
0
 

Author Comment

by:Worldsplayground
ID: 39213688
Okay,
How about this way ..., I am not sure if this is possible, but when we enter the job details, we do it the following way ...
The first line in the cell is the client's name.
The second line after pressing (ALT + Return) is the job description

Is it possible for this to work?

At the moment this would not work for me given that there is often a clients name and job description.

If we maybe had an equal sign after the clients name to create a separator, so that the function only searches and matches the name before the sign.

Would this work.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39214264
yes that would work.
search the job details for a separator   = or /    
 if found, only use the part to the left as Client.  if not found, try to use all of details as client.... ?

will try an update tomorrow as now late for me...
0
 

Author Comment

by:Worldsplayground
ID: 39214270
Great, that sounds good. Let's make the separator three consecutive dashes
---
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39218364
        'update 05
        'split the client name/job description cell based upon delimiters
        checkname = Split(clientnm_descr, "---")
        
        ratechg = GetZonePrice(Trim(checkname(0)), pusuburb, dosuburb)
        JobNo = ratechg

Open in new window


could add further checks if not found but best to stick to one simple one... trims off spaces in case as well.  ie
steve ---was here
01-Unity-Invoice-Template-04b.xls
0
 

Author Comment

by:Worldsplayground
ID: 39220790
Thank you for adding the delimiter.
There still is an problem with this updated version.

If I have the name and then the delimiter ---, for instance Steve --- job description
the code works.

However this is not the setup of how the invoice is entered.  The first line in the cell is the Client's name, then the 2nd line of the cell is the job description.  We of course press ALT + ENTER to type in the 2nd line.  For instance ...
Steve
--- job description

When we do it like this, it does not work.  Is there a way for this to occur.  The delimiter must always start on a new line in the cell.

Also, the error message #VALUE! appears in the cells unused again.  Could we have this fixed please as well.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39221867
ok, hadnt realised you did that. should have added to your example.

now splits on both.
        'update 04c
        'split the client name/job description cell based upon delimiters
        'try crlf
        checkname = Split(clientnm_descr, vbLf)
        If UBound(checkname) = 0 Then
            'try ---
            checkname = Split(clientnm_descr, "---")
        End If

Open in new window

01-Unity-Invoice-Template-04c.xls
0
 

Author Comment

by:Worldsplayground
ID: 39221925
I did mention it, in a previous reply
"The first line in the cell is the client's name.
The second line after pressing (ALT + Return) is the job description

Is it possible for this to work?

At the moment this would not work for me given that there is often a clients name and job description.

If we maybe had an equal sign after the clients name to create a separator, so that the function only searches and matches the name before the sign. "

Anyway, let me test it and get back to you.
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 39222067
it just uses the first delimiter one it finds, in order of importance.
first looks for the LineFeed and if not found, looks for ---
0
 

Author Comment

by:Worldsplayground
ID: 39236802
Hi Robberbaron,
Thank you for work.  I have tested most of the variables and they all seem to work with the new code.

The problem that now happens is that the #VALUE! error message appears in all cells not yet filled in.  This stops the calculation of the amounts.

Can this be fixed, so that the #VALUE! does not appear.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 39244245
yes ,  needed a check for blank input
        'update 04d
        'split the client name/job description cell based upon delimiters
        'try crlf. return blank if checkname is empty
        checkname = Split(clientnm_descr, vbLf)
        If UBound(checkname) = 0 Then
            'try ---
            checkname = Split(clientnm_descr, "---")
        End If
        
        If UBound(checkname) <> -1 Then
            ratechg = GetZonePrice(Trim(checkname(0)), pusuburb, dosuburb)
            JobNo = ratechg
         Else
            JobNo = ""
        End If

Open in new window

01-Unity-Invoice-Template-04d.xls
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now