populate a cell if another cell begins with ?

[cmorten82]

I am trying to figure out how to populate Column N with the Left 4 characters of column F but only if the Left four characters are in the list I specify otherwise Put a "N/A" in the cell and it needs to carry down to the last record. I also need the list to live within the code and after the code is ran I need it to do a Trim on that column.

[jaslake]

Hi cmorten82

Is there a reason you want the List to live within the Code?

I also need the list to live within the code

[cmorten82]

Im still pretty mew to vba but if i understand correctly, if the list isnt in the code than the code would have to reference a table right? I have one workbook that i have all my VBA in and then i have added them to my ribbon and hidden the workbook. So if i run the macro in the workbook i am formatting would the table have to be in the workbook i am currently in or could it be in the workbook where all my VBA is saved,

[jaslake]

Hi cmorten82

This can certainly be done but if you wished to add/delete/change the Unit Code you'll need to change the Macro Code.

I also need the list to live within the code

I'd suggest this approach:

We'll call this "Code Book" for purposes of discussion...

could it be in the workbook where all my VBA is saved

The short answer is "Yes"...I'd suggest creating a Dynamic Named Range in "Code Book" in a Sheet Named "Lists" that contains all of your "Unit Codes".

Using this approach you would not need to modify the Code in the event you wished to add/delete/change Unit Codes. You would need only to add/delete/change the Unit Code in the Named Range (Unit Codes) in the "Code Book". Since the Named Range (Unit Codes) is Dynamic it'll be updated automatically.

If you wish to pursue this approach please show me your "Code Book" and your Raw Data File. I'll not modify your Ribbon...you'll need to deal with that.

[jaslake]

Hi cmorten82

The attached demonstrates my point in Post #4.

For purposes of testing place both attached Files in the SAME Folder (this can be changed based on your actual environment).

Open Code Book ONLY...Click the Button. You'll be asked to open the Data File (12.7.16.xlsx)

Let me know of issues.

[cmorten82]

Thanks for all the help. So currently the workbook that houses all my code is hidden and saved that way. Then I have access to all my macros through the ribbon. When
I run a macro it runs it in the background without the VBA file ever being visible. This is the standard I would like to use. The current Data file is a download from Quick Books.
I have separate code for formatting step so I have a separate tab for just the QuickBooks download formatting.

Based on my understanding of your code I run the macro in the workbook where the macros are saved? It then asks me to locate the file that I want to format which
is the 12.7.16 Excel file I choose that file and then the code runs for that file? Am I correct so far?

My hope would be that I open the download, go up to the ribbon and find the Button that will run that macro then I click it and it runs in the background.

I have attached the VBA file and the original dataset. In the VBA file you will notice a few codes that are titled QB_DT_"Some unique name". My goal is to format each tab in the following way:

Remove the blank columns, add column headings, Add the Period and the month that correlates with that period, then fill certain columns with specific data (i.e. "INC", EXP", Fiscal Year, Division Name, etc.)
The next step is to take the Debit and Credit column and merge that into one field with the Debit staying positive and the Credits turning into negatives and the last step would be to pull the
unit code from the beginning of the memo column and put it in its own column and then take those 5 digit account numbers and put them in there own column but not every column has a unit code and an
account number so when there is nothing I want it to do nothing. the data that is left in the memo field is going to then be called Description and the Unit code would go into its own column and the account
number in its own column and if there was no Unit code or account number the cell would just be blank.

[jaslake]

Hi cmorten82

I assume when you say the Workbook that houses all the Code is hidden you mean it is locked for editing and is password protected.

I've modified the Code I previously provided such that it will work on the Active Workbook and have added it to your Quick Macros.xlsm.
In Module2:

Please Login or Register  to view this content.

I've not tested the Code on your Book8.xlsx simply because I have no clue how you get from here to there. Please note this...

Please Login or Register  to view this content.

[cmorten82]

Thanks again for the help. This worked!!!

As for the hidden workbook if you go to the view tab up in the ribbon and under the "Window" section there is a button to "hide" the workbook or "Unhide" the workbook after I am done make changes or addition to my VBA workbook go to "File" > "Options" > Customize Ribbon and add a new button for the new macro and then I select hide and then save it that way. Now any workbook that I open has the new buttons I have added on the ribbon in its own tab. When I run any macro it opens the workbook in the background without the workbook ever being visible.

Thanks again for the help it is much appreciated.

[jaslake]

You're welcome...glad I could help. Thanks for the Rep.