Posts by CapnAmazing

    Re: Find Cells With Underlined Text


    Thank you. Is there a way to search underlined words within a VBA array? I guess maybe if I'm more specific it may help you better answer my question. I just figured it was more simple than it seems to be.


    Here's basically what I'm trying to accomplish:


    Code
    for i = 1 to ubound(array1)
      If array(i,1) is underlined then
          strUnderlinedRow = array(i,1)
      Else
          <other specific code>
      End if
    next i

    I'm working on a macro that requires me to loop through a column and stop every time the cell has underlined text. Can someone help me out with this code? My code currently looks like this:


    Code
    For i = 1 to 5
        If cells(i,1).font.underline = xlunderlinestylesingle then
           strUnderlineText = Cells(i,1)
        End if
    next i

    Re: Matching Line Items Up


    I would love to change the way I receive the financial data, but unfortunately I am lucky to be able to get it in excel at all with the software our company uses. And since I have to update 50 properties every month, putting together the macro would definitely be a time saver eventually.


    If you even have ideas on the way a macro like this could work, that would be helpful. I can put in the code behind it. I'm just trying to think through the logic.

    Re: Matching Line Items Up


    That is a good idea. However, I guess I should mention that sometimes the new month will have line items not already included in the historical section. In that case I have to add the new line item.

    I am looking for help with a macro that will match up line items from two different spreadsheets. Basically I have a master accounting sheet which has the Income and Expenses for a building that dates back quite a ways. Each month I grab the financials and add it to the master sheet. The problem is that the master sheet has many more line items than the current month, so it takes me 5 or 10 minutes to match up the line items.


    I have attached an example. The "Master (Before)" sheet is the historical financials. The last two columns are the new month before it has been lined up. The "Master (After)" sheet is what it should look like after the line items are matched up.


    forum.ozgrid.com/index.php?attachment/24982/

    Re: Finding cells with comments


    Thanks for cleaning that up for me.


    However, I guess I don't really understand what the code you mentioned does for me. I just want to be able to capture the text within the comment of a particular cell and place that text somewhere else.


    For instance, if cell A1 had a comment with text, how would I take the text from that comment and place that text in column B1?

    I am looking for a way to loop through a spreadsheet and find all cells within a certain column that have a comment. Is this possible?


    It will probably look something like this:

    Code
    For i = 1 to finalrow
       If Cells(i,1) [COLOR=Red]<has a comment>[/COLOR] then
           Do an action
       Else
           Do a different action
       end if
    next i


    Can someone help me with this? Thank you.

    I am running a macro that incorporates numerous workbooks. Sometimes some of those workbooks referenced in the macro do not exist (depending on the situation). Can someone please help me with some simple code to check whether a workbook exists?


    For example:

    Code
    If workbook exists then
        'Do an action
    Else
        'Do a different action
    end if


    Thanks!

    Re: Macro to open Word and Start Mail Merge


    Wedgetail -


    Apparently, my code doesn't like the following syntax:


    Code
    set WdDoc = .documents.open Filename:="C:/30DAYLTR - Template1.doc"


    I have tried it a few different ways but it doesn't like it. Can you help with this?

    Re: Macro to open Word and Start Mail Merge


    I have been trying to use mail merge without success. I used the code provided in this thread (with different directory paths for my particular files) but I keep getting the same error:


    "Run-time error 438:
    Object doesn't support this property or method."


    Can anyone help me figure out why?


    I have posted the the excel data file and the word template.
    Please help.

    Re: creating a macro that uses word and exc.


    I just found out that if I want to open up Microsoft Word, I add the following code:


    Code
    Application.ActivateMicrosoftApp xlMicrosoftWord


    Actually doing something in Microsoft Word is another story. Please help.


    Thanks in advance.

    Re: creating a macro that uses word and exc.


    I thought there was a way to mix an excel macro with something like Microsoft Word. This looks like it's running some other app besides a microsoft application with built in macros. I just need the syntax to be able to open microsoft word and run the mail merge function, pointing the database file to the excel file that was just manipulated.

    I have created a macro that manipulates some data in excel. Once the data has been manipulated I open up Microsoft Word and run Mail Merge, and point the database to the excel file.


    I would like to automate this if possible with one macro. How would I take my existing excel macro one step further to include opening Microsoft Word and running mail merge?

    I have a city, state and zip in one cell that I would like to split into 3 cells. The problem is that some zip codes are 9 digits and others are 5. Also, the cities can sometimes be more than one word. The states are just the 2 letter code. Everything is separated by spaces.


    When I use the Split function, it will split it up nicely, except for when the city is more than one word. The split function allows you to choose how many substrings you want it to return. However, it will always split it from left to right. If I could get it to split from right to left, I could set it to split into 3 substrings. Does anyone know if this is possible?