Posts by CapnAmazing

  • Find Cells With Underlined Text

    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

  • Find Cells With Underlined Text

    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

  • Match Up Data Across Workbooks

    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.

  • Match Up Data Across Workbooks

    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.

  • Match Up Data Across Workbooks

    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/

  • Finding cells with comments

    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?

  • Finding cells with comments

    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.

  • Checking if a workbook exists

    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!

  • Macro to open Word and Start Mail Merge

    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?

  • Macro to open Word and Start Mail Merge

    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.

  • creating a macro that uses word and exc.

    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.

  • creating a macro that uses word and exc.

    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.

  • creating a macro that uses word and exc.

    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?

  • Using the Split function

    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?