Way to Format row if certain text is found in a column.

I have some information that gets populated on one sheet in Range (A10:E52) Columns (A-E) are merged together:

What I want is if the text in That row 10-52 in column A = contains Certain text then it would Format the row a certain Way.

text to match is: (These are Descriptions that get brought through so I would like if they exist to change the format to have a better look.

Operating/Standby Charges
Inspections
3rd Party Tools/Subs
Miscellaneous Charges
Core Charges

example for row 10: if that text is found then format : Column F10:O10 (delete all formulas and formatting and shade the column A grey color and hopefully take the text and Merge the entire row and center text.
End Format would look like this:

Please Login or Register  to view this content.

I just don't know how to look for the 5 categories and then tell it to format the row in this manor.

Attached sheet and on the job Ticket Tab an example of the ending result is in row 10.

I would like row (10,12,13,15,16,17) to have the same format as row 10.

Here is another thing they row #'s change based on the info that gets populated so It would have to look at each line and based on the text know whether to format that way or not.

I was able to use Conditional formatting to get the output the way I wanted it so that looks good now.

Heres another question:
I use the Formula to bring in the rows that get data contained with in them and your code matches the date serviced real good and it looks good.

Is there a way that if a category has no choices used with in the drop down it has then it does not bring in that category which in turn leaves more room for other choices in other category's.
My Formula I use is

Please Login or Register  to view this content.

your code is

Please Login or Register  to view this content.

Yes the Categories will have a predetermined set of drop downs to choose from - At times say for example in the Core Charges category there may be no drop downs selected because there are no Core Charges to be applied.

What I am wondering is can the code check if any of the drop downs have been selected and if not then when that category would normally show on the Job ticket that it gets omitted since there are no charges being applied for that category.

That way the Job ticket does not show a category with no Charges.

i hope this helps explain my quesiton.

The drop downs following each category are allocated for that category. Do I need to allocate them a different way.

Update: Cancel that I had a major change : The items that we manually input (Comments, tool size, quantity & unit per) to each service would get out of line if we made an addition and everything got either shifted down or up -- then the items we manually inputted would be on the wrong line......So I am moving the necessary data to be filled out onto the Daily chgs tab and then we will have to bring all that data over so everything lines up I will send you what I have so the code can be altered to accomplish this and you have a better Idea what will need to be done. Once I have it done I will resend it to you.

OK Here is the New format the sheets are still same it is just the user will input all the data onto the Daily Chgs tab and then that will populate the Job Ticket.

On the Job Ticket your columns are:

Services/Tools Provided Including Sale Items = Same as before 'Daily Chgs' Column B
Tools Provided/Comments = Tools Provided/Comments
Tool Size = Tool Size
Dates of Service = Same as you did before
Quantity = Quantity
Unit Per = Unit Per
Price = Rate

This way it all gets populated and all the Manual information stays with the correct row.

Wait on my next attachment I am moving the header Info to the same page so they only enter info on one sheet......

Hopefully you have started yet.

Ok Sorry Lets go with this one If I make changes it wont alter the columns where the pertinent data is.

Thanks I will keep making a few things I can and wait.. Hopefully it will not be difficult for you..again thanks for your help, but this will cover a lot of the things i was trying to accomplish.

I can't tell if I emailed that version last night but it should be pretty close to my last one attached

Let me know when you get a chance to work on it

Everyone can't wait to put it to the test

Thankz

Here is the latest & greatest hopefully you get a chance to look at it tonight.

I'll look as soon as I get home tonight

Hey its almost there .... I put the code into Module 4 and it just needs a coupe things moved around.

1. the rate or price is coming through in the Tool & comments section
2. the comments did not come through at all.

So If I could get the 'rate' moved to column N on Job Ticket and then the 'Tools Provided/Comments' placed into Column F on the Job Ticket.

Sorry but I got into the office and showed how things were coming and they wanted to move some columns around on the daily Chgs tab - with me not really understanding how to manipulate the code I can not make this change myself.

This is what the columns on the daily chgs tab should match on Job Ticket

Job Ticket Columns:
Services/Tools Provided Column (B:F) on Daily Chgs
Tools Provided/Comments Column (I) on Daily Chgs
Tool Size Column (J) on Daily Chgs
Dates of Service You Know this one already
Quantity Column (K) on Daily Chgs
Unit Per Column (G) on Daily Chgs
Price Column (H) on Daily Chgs
Total Nothing its a formula that calculates on Job Ticket

Thanks Again

I am sorry but they wanted to add a piece of code that looked for duplicates and prevented the user from choosing a duplicate from the drop downs.

The code that got inserted actually also inserted a row at (67) and it uses the color variation ion the code to stop duplicates form happening.

Once I started trying it out I got a error on line saying object variable or with block variable not set
( lc = wd.Rows(8).Find("tot", MatchCase:=False).Column - 1 )

I tried making a couple changes but I could not get to work right.

What change do I need to make for this to work correctly now?

Also if you have a better way to prevent duplicates please let me know.

I attached the worksheet

I had help with the duplicate code to change it and not alter the sheet but I still get this error from Module 3 where your code is.



It gives me 'Object variable or With block variable not set'
( lc = wd.Rows(8).Find("tot", MatchCase:=False).Column - 1 )

on that same line

here is the sheet - I cant figure out on my own

I don't get the error when the duplicate code is not added for the change event on the sheet

I may have sent the wrong sheet...I will reattach

I wanted to prevent the user from choosing a duplicate from the drop down lists on the sheet in column B.

Some of our field hands have used the same drop down twice in the past...I wanted to prevent that from happening.

If you open and go under column B - choose the drop down in row 14 column B & choose "Directional Operating Rate (No MWD)" Which has already been used in row 10-

The code prevents them from doing this.

But then with that code running if you go to the job ticket sheet you will get the error I described.....

I just tried running the sheet if you actually had 2 of the same drop downs chosen and when you do it does not populate the Dates serviced properly on the Job Ticket page

Daily Chgs tab - row 11 has Directional Driller Engineer - Per Day, Per Man
Duplicate that same choice on on Row 14 and you will see that the dates serviced get missed when it populates the Job Ticket Page.

You will have a blank on row 13 on the Job Ticket and only the Dates of service will be on line 15


since there are 2 drop down with the same description when it populates the dates one of the dates of service gets left out.

That is why I wanted to prevent the user from being able to have the same selection twice.

Okay, Again I found an issue and it has to do with the duplicate code. I had to add a row at 67 on the daily chgs tab and the duplicate code uses the color formatting for its range now. The other Range I used would make the message a duplicate was used everytime.

Now the duplicates is working at 95% (the problem it has is that if the user clears out data using Clear content or delete I get a error type Mismatch - I dont want the error)

but now when I go to populate the Job ticket I get a runtime error 1004 we cant do that to a merged cell .......pointing to line

Please Login or Register  to view this content.

Can you make a change for this to happen properly?

Or better yet can you write a code that will do the same for looking for duplicate entries in column B and prevent them from happening. It seems that everytime I get one working it has an issue with the other one.

I would rather not have the row 67 on the Daily Chgs tab Because I just have to reduce it to a row hgt of '1' so not to see it for the duplicate to work - Hiding it prevents the code form working in the last grouping.

Here is the revised sheet

I tried a change of

Please Login or Register  to view this content.

I changed from -1 to -2 and now it seems to be working.....I also got the type mismatch to correct also I added

Please Login or Register  to view this content.

Everything seems to be working for now....thanks again

Here is a new one-- we have software that our guys run all day long in the field.


It changes the Regional date setting from (mm/dd/yyyy) to (yyyy/MM/dd) which throws off the "Dates of Service" on the Job ticket when they Come thru.

Usually the dates look like (09/12-09/21) after populating on the Job Ticket
Now they Show (2014/09-2014/09)

Can you update the code for this other format so the dates come thru properly.

Please Login or Register  to view this content.

Okay Here is the Problem you don't have the software to run this scenario to recreate the situation so I am going to do my best and explain.

Look at the Snapshots I attached there are 4 of them

1. As long as you do not have this software running on your laptop your Region & Language settings remain the way you have them (EX: PIC 1)
1A. this is how the output looks correctly on the Job Ticket (EX: PIC 2)

2. When you have the software running (EX: PIC 3)
2A. This is how it looks with the program running (EX: PIC 4)

We need the date that comes thru in column I:K on the job Ticket to have (Month/day) for its date ranges (09/12-09/21) Not (2014/09-2014/09)

This software is almost always running that is the problem

Can we just modify so it will always Show Month/Day no matter what the regional settings reflect

I don't know if you were able to understand what it is doing, But I added a macro that would reformat your region setting on your computer to the dates would come thru Correctly. I can use this until I get a way to format the code to let it come thru correctly when that software adjusts the short date setting.

Here is the code I used

Please Login or Register  to view this content.

I do have another issue: I had to add another row to the Daily Chgs tab at Row 8 which in turn pushed everything down a row and now my job ticket wont populate.

I have attached the sheet I am not sure what all to alter on the (NewDateRange in Module PopulateJobTicket)

Please Help.

It is giving me Invalid Procedure call or argument on line

Please Login or Register  to view this content.

It appears to be working I will have to test it out this week end...

Also If you could please when get time could you go thru and comment through the code So I can make changes when these things come up (Adding and removing Rows/columns) ...that way if you are not around I can figure it out on my own.

Thank you

Sorry, here is one more thing I would like to add if possible On the Daily Chgs Tab ...you know how we have 6 Categories with independent drop down in each.

Right now if there is nothing chosen in a category then when it brings everything over it brings that category title with nothing under it. I have added a NONE choice into the drop downs that works to help it look better on the Job Ticket once it is populated.

What I would like to have happen is if there is nothing in that range Below each title then it would omit that category title to not show on the Job Ticket page.

Is this possible.

I am not sure if you saw my post from yesterday -- I am just not sure if that is a possibility, Just let me know.

But I was running a test on the final sheet and I am not sure what changed over time but when I only have one entry in certain columns on Daily Chgs & when it pulls the date through it is not populating correctly.

I attached sheet so you can see the issue look at the job ticket the way it formats if only there is one date to list under the date range.

Remember Financial3 is protection PW

I put back in the code below from an earlier version and it is working I just commented the other out so you could see.....I gather this is ok and wont pose any problems.

Could you let me know if this is OK and what I asked about yesterday on leaving a category out if nothing chosen with in that range?

Please Login or Register  to view this content.

I just realized that the cell above is a bordering cell so do you think I should try to add a blank line but reduce it above each category that way it wont think it is filled in...or do you have a better solution

It open right up for me I am not sure why ...I have re-saved and reattached

Let me know if this works

I have added the blank Lines and Hidden them and the sheet is populating OK I just had to changes some ranges but if you come up with anything lease let me know but seems to be working for the most part once I finish changing everything

I'll try it at home tonight I'll let you know

I just got thru testing it out & I thought it was working but then I filled in entire sheet and verified it populated job ticket correctly then I omitted a Category by clearing all its data and went back to Job ticket.

It actually omits the last drop down choice in the section just above the category when it populates the Job ticket

It has 2 blank lines where the category would usually go. I assume that would be for the line that is omitted and the category line that it does not show.

I attached the sheet so you could see what I did.

All right I just got it copied and after running the test the only thing I see is the range does not capture the last Column on the daily chrgs sheet. If you
actually have the last column checked then it will not populate the job ticket on that line....

Look at column BU and notice the spots that don't have a "1" in them they are the ones that come through on the Job ticket....the one with the "1" don't show any dates once you populate the Job Ticket.

I attached the file so you can see it

Everything is going great I would like to add one more change and it should not be very hard to do.

In (A7) of the Job Ticket right now I have it showing the start Date.
What I would like to show is the Period Covered on the Job.

The Start Date is in Cell (A6) of the 'Daily Chgs' Tab right now.

What formula would I use to actually grad the start date & the last date that any activity was generated on.

Example for the sheet I have attached the Period Covered is ( 09/12/14 - 11/12/14 )

What would I need to use to reflect this range in that Cell (A7).

Believe it or not this is the first problem one of my hands had today after trying to get the sheet filled out.

When it goes to populate the Job Ticket it got an invalid procedure or call on Line,
If DS <> "" Then DS = Left(DS, Len(DS) - 2)

The only thing I could see is they did not have any thing filled out in the Comment section and no dates which happens from time to time:

After I filled in some comments the invalid procedure went away and it populated.

Also when it hung up and did not follow through with the procedure The sheet had been unlocked during the Macro but then when it Messed up... it left the sheet unprotected ....And inadvertently the guy started playing around with it trying to get it to work and no idea what he was doing.

So is it possible when the Macro Hangs up at any time can it automatically Lock the sheet for this not to happen.

Please Login or Register  to view this content.

What did you find that was happening? And were you able to add a way that if it stops the macro for some reason it would still keep the destination sheet locked.

Thanks ONCE AGAIN

Let me know if you enhance the error code so I can update the ones that I have already using this in the field.

thanks