Posts by dsutton

  • Add pre-defined text to a cell based on input from a different cell

    I want to have VBA code add specific words to a cell (B2), based on a string of letters in a different cell (A1)...


    As an example I have 10 buildings, each represented by a string of letters. In cell A1, I want to input the building designation (ie: "bnumber3".. but each building number has a different text string), and then I want a pre-defined text string to populate cell B2... something like "3 bedroom and 3 garages".


    There is already some vba in the file, but not performing the functionality mentioned above.


    Thanks!

  • VBA code to highlight characters greater than a specified count within a cell

    Carim,


    This is very close (and exactly what I had described), but I would like the letters to stay black on those that are equal to or less than the count in C2 and only showing Red on the characters greater than C2.


    When the count in B2 exceeds the count in C2, I go back into B2 and delete the excess characters...so would like the Red text to only show on the text that exceeds C2. Currently clicking on the Adjust button shows the Red excess characters, but when I delete characters within B2 other than the Red text , the original letters are currently staying Red no matter how many times I click the Adjust button.


    Thanks!

  • VBA code to highlight characters greater than a specified count within a cell

    I am using Excel as an aid for recording "time limited" scripts. I currently paste a script/text into cell (B2) and have Excel calculate and show the word count (in D2) as well as the total character count (in E2). In cell C2, Excel shows what the character count should be for the desired recording time based on the prior results. I would like the specific characters within cell B2 to be Red if the count in E2 is greater than the number in C2. As an example...if the character count in cell B2 is greater than the number in cell C2, then specific characters in B2 that are greater than the number in C2..... should be Red. Additionally, if the script pasted into cell B2, includes the letter string "mph", I would like mph to be changed to "miles per hour"..I currently change the "mph" letter string manually using Find/Select/Replace. What would be the code in VBA to produce the correct colored characters in cell B2 as well as the "mph" string conversion?

  • Transpose Multi-Column Groups/Repeats Based On 1 Column

    Re: Evaluate cell then copy and paste special


    Thanks Krishnakumar,


    This works fantastic on the limited rows I had posted in my message. But when I use it on a larger set of items, it breaks at row 22. I initially thought it was due simply to more rows, but after deleting the value in row 23 through row 179, the code works fine on all other values that appear after that.


    I am attaching another file with more rows and the culprit text that begins in row 23.

  • Transpose Multi-Column Groups/Repeats Based On 1 Column

    I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A.


    As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.


    This then would need to loop all the way to the bottom of the data in Column A looking for a change in value.


    The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)

  • Sort Words Within Cell

    I have a file with 12,000 user records (one user per row). Within each row there is a cell that contains all of the product that each user sells. The product within each cell is listed between commas.


    I need to Alphabetically sort the product names within each cell and have the data remain in that particular cell.


    So as an example, in cell A12, I have something that looks like this:


    Apples, Zucchini, Pumpkins, Beans, Squash, Corn



    I need it to read: Apples, Beans, Corn, Pumpkins, Squash, Zucchini


    Thanks

  • Attached Sample - Calendar

    Re: Attached Sample - Calendar


    This is an outstanding tool for creating a calendar with task's, but some of the tasks in my list are for 13 to 14 months in the future. This code places all tasks for a particular month in the same calendar page regardless of the year. Can you add a dialog box or coding to create say a 14 month calendar and then have the header reflect month and year when it prints?

  • VBA locate text string, cut and paste

    Re: VBA locate text string, cut and paste


    Batman,


    Thanks for the reply. Your solution would solve for what I posted, but as is normal in my case I dodn't explain clearly enough.


    The string of criteria to search for could be as many as 100 different variations, and any one of these strings may exist in the original string that needs to be queried. Probably what would work best would be for me to have a column of criteria to search for in a second tab, have the code loop through this list and search for each string in the first tab that contains the 30,000 rows. When it finds the string, cut and paste to a column four to the right of the column in which it found the string (in the first tab).


    So tab "A" has 30,000 rows with a column containing strings of 20 to 30 characters. tab "B" has a colum of 100 rows containing the text string to search for. The code would look at the first cell in tab "B", loop though all 30,000 rows in tab "A" , extract the string if it finds it, cut it from the cell and past it four columns to the right. Then it looks for the criteria string listed in the next cell down of tab "B", etc,etc.


    Thanks again

  • VBA locate text string, cut and paste

    I have a spreadsheet with 30,000 rows and four columns. The cells contain text strings as well as commas and numbers. I need to locate specific text at the left side of the cells in column B, cut it from the string and place it in an empty cell five columns to the right. I then need to look for a string of text and numbers at the right side of the cells in column B and then cut and paste that six columns to the right. I need it to search from the top of the column all the way to the bottom. Also would like to be able to specify the search string via a dialog box.