ISSUES: Formatting, Converting, Trimming, Comparing.

[pyrosaxplayer]

Alright folks of this wonderful forum.

I need your helpful assitance.

My boss wants an easier way to bounce two employee charge numbers from two different excel workbooks to make sure both timekeeping systems are reporting properly.

I have about 2-3 days clocked in trying to gather as much information about this but i keep hitting roadblocks. I hate wasting mine and his time. (My priority is Database not VBA but boss says do it, you gotta do it LOL)

Refrence=
Charge Number (Cell = G2:G99999): 300000039143

Issue 1: I was able create a code that grabs a range and formats it to Text. Problem is that inorder for it to change to the format i still have to click inside every single cell. annndd it changes to 30E+11 blah blah. I have changed my code to every possible format but i still have to click inside the cell for changes to even happen.

Issue 2: Since my time was focused on the formatting and converting, I barely had time to look at how to trim the first 5 characters, (On the other workbook itll be trim first 5 and last 1)

Issue 3: Lastly I will need to bounce both workbooks together to ensure employee A charged N Hours to Charge Code of XXXXXXX

If you need any more information let me know. I would be happy to provide an example spreadsheet if its possible here.

[jaslake]

Hi pyrosaxplayer

Welcome to the Forum!!!

Yes the Forum allows (and I encourage) attaching of Sample Files.

Please attach a sample file that represents what you have. The structure of your attachment should be the same structure as your actual data. Any proprietary information should be changed.

Include in the attachment any code you're currently using (whether it works or not) and an "After" worksheet that demonstrates what you wish the output to be.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the "Add Files"... button to locate your file for uploading.
6. This will open a new window File Upload...Click "Select Files"
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the "Upload Files" button and wait until the file has uploaded.
10. Click the "Done" Button.

[pyrosaxplayer]

Glad to be here!

I have attached the first workbook example. I am working on getting the other workbook example. (However, just solving my first 2 issues, i should be able to accomplish the third one with ease, I have done comparisons before through MS Access, So i assume the VB code is relatively similar.)

I appreciate your help!

[jaslake]

Hi pyrosaxplayer

Is Sheets Start what your Raw Data looks like? I ask because you said this and did not include Code in the Sample File.

I was able create a code that grabs a range and formats it to Text

[pyrosaxplayer]

Apologize about that. and yes, "start" would be RAW and "Finish" would of course be the final result i would like

Here you go. Changed to .xlsm

and added my code

[jaslake]

Hi pyrosaxplayer

Try this Code in the attached...CTRL + x will fire the Code

Please Login or Register  to view this content.

[pyrosaxplayer]

WOW WOW WOW!

There was no way I could have done that as quick as you did! Thank you JASLAKE!

Quick Question. What would I change out in order for it to apply to the below conditions

1: Change Sheet names so that it can be inserted in the other workbook and apply
I would presume this part of the code

Please Login or Register  to view this content.

2: Change the amount of text removed on both front and back of cell. Example. Remove first 5 Character and Last 1 character

[jaslake]

Hi pyrosaxplayer

Well, you're question begs a question.

This Question

Change Sheet names so that it can be inserted in the other workbook and apply

Makes me ask this question: Is the Sheet Name ALWAYS the SAME? Or do you need to CHOOSE the Sheet Name?

Regarding this...it'll take, I think, an additional line of Code

Change the amount of text removed on both front and back of cell. Example. Remove first 5 Character and Last 1 character

[pyrosaxplayer]

Unfortunately, The sheet names will probably be different BUT if it takes some code adjusting for that, i can always easily just rename the sheet lol

For the text amount, okay, so in the event i want to switch it over, what do I insert/replace in the orig code?

[jaslake]

Hi pyrosaxplayer

If you're going to be running the Code on multiple Files I'd suggest placing the Code in a separate "Code Book".

Download the two attached Files and place them in the same Folder. Open Code Book and click the Button. The Code will ask you to select the File that contains your Raw Data.

The Code assumes the Raw Data Worksheet is the First Tab in the Raw Data Workbook. The Name of the Raw Data Worksheet can be any valid Name.

The Code will add a Worksheet named "Finish" to the Raw Data Workbook if it does not already exist. In the Code you can change "Finish" to whatever you like.

For this...I've added the indicated line of Code

For the text amount, okay, so in the event i want to switch it over, what do I insert/replace in the orig code?

Please Login or Register  to view this content.

[pyrosaxplayer]

Perfect! Everything is working perfect Thank you again Jaslake

[jaslake]

You're welcome...glad I could help.

If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.