Posts by smuzoen

    Re: Select the last cell with a value and edit macro


    I hope this helps. Without the actual excel sheet it is a little more difficult but the following should help you along
    To dynamically find a range you could use code such as this

    Code
    Dim lastRow As Integer
    Dim lastCol As Integer
    lastRow = Cells(Rows.Count, "A").End(xlUp).Row
    lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
    Dim xRange As Range
    Set xRange = Range("A1", Cells(lastRow, lastCol))
    xRange.Select


    This will select the range you wish to copy no matter how many rows there are or how many columns assuming the data starts in cell A1


    To past the values and their formats you would use something like this

    Code
    Sheets("Data").Select
        Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
            xlNone, SkipBlanks:=False, Transpose:=False


    If this does not help upload the spreadsheet with some data and it would make things easier as I think in your code you have a lot of commands which you probably don't need. I assume this was from a macro recording?


    Hope this helps but if not as I said upload a sample sheet and I will sort it out for you
    Anthony

    Re: Hide rows based on date and colour


    I have added the code to your claims spreadsheet - see if this works for you. I have tried it and it works for me.
    Let me know


    Hope this works
    Anthony

    Re: Hide rows based on date and colour


    This should do what you want - hide rows where cell in column A is filled green and the date in column C is not in the current month.
    You have done most of the work so your macro only needs a little tweek.



    Hope this helps
    Anthony

    Re: Multiply each cell in a range to another range


    I think this is what you want - essentially multiply every number in column B by the first number in column A, then multiply every number in column B by second number in column A and so on - so no matter how long column A and column B is, every number in column A has to be multiplied by every number in column B and the numbers to be placed sequentially into column C.



    I hope this is what you want
    Anthony

    Re: Find Text in String and Replace it with the other Text in the same String


    You can unhide all the columns prior to executing the text substitution. The new code is as follows. As you see it only needs one more line of code.



    You can the hide the columns after doing the substitution if you want to using something like


    Code
    Columns("C:C").Select
        Selection.EntireColumn.Hidden = True


    Hope this is what you want
    Anthony

    Re: Search and retrieve information from columns in Excel into list view


    I think this should do what you want - I have the code below for a form but I also have attached a macro enabled workbook so you can see how it works. Let me know if it needs to be altered or needs other functionality. It has a text box to enter your search term, a listbox for results and 2 buttons (search button and a cancel button) - open up the VBA editor and have a look at the code behind the form. There is some code in Workbook to load the form when the spreadsheet opens. I have some data in the sheet as well so you can try it out.



    There is some more code for the cancel button and some code to load the form on opening the spreadsheet.
    Let me know if you need it modified. Hope it helps
    Anthony

    Re: VBA Editor word wrap for .Body text


    If you have a long line of text you can wrap it in the editor by doing the following


    With the hyperlink are you using the vba editor in excel or outlook? If you want outlook vba code you are in the wrong forum.
    If you are after Outlook VBA help post it into the Excel and/or Email Help Forum http://www.ozgrid.com/forum/forumdisplay.php?f=10


    Hope this helps
    Anthony

    Re: Find Text in String and Replace it with the other Text in the same String


    This should do what you want - it will replace all instances of the the value in A1 with the values in A2 - so if the text in A1 occurs more than once in a specific cell it will replace all the instances of this. If I have not understood what you want just let me know



    Hope this helps
    Anthony

    Re: Copy Data from one sheet and insert to the end of a table on another sheet


    You can paste as values but to preserve the format you will need to use the following



    Is that what you need? If you dont keep the xlPasteFormats command you will lose the format of your dates and times in the spreadsheet. If I am missing want you want let me know in more detail.
    Hope this is what you want
    Anthony

    Re: Order list of dates and adjacent values using formula


    When you say you need a formula I assume you mean a macro? The following macro will sort your list into date order.



    Open up Visual Basic Editor (on developer tab) and insert a module. Paste the code into the editor window then close the editor. To run the macro select Macros in the developer tab and select the sortData macro and run it.


    Hope this helps
    Anthony

    Re: Copy Data from one sheet and insert to the end of a table on another sheet


    The process is fairly much the same. For this to work it assumes that the second workbook is open at the same time as the first workbook. Just substitute the workbook name for the second workbook into the following code.



    Hope this helps
    Anthony


    PS: If the other workbook is closed you can do the following

    Code
    Workbooks.Open Filename:="C:\Path to second workbook.xlsx", _
            UpdateLinks:=0

    Re: Copy Data from one sheet and insert to the end of a table on another sheet


    The following code will do what you want.



    Hope this helps
    Anthony

    Re: Convert Text to Number within a column


    Lets assume you have the following in column A
    $491.80M
    $2.06B
    The following code will convert the text in Column A and put the real value in Column B. The assumptions are that each revenue entry starts with a "$" and ends with with "B" or "M"



    I hope this helps
    Anthony


    PS: You could do this without VBA using the following conditional statement within the worksheet

    Code
    =IF(RIGHT(RC[-1],1)="M",MID(RC[-1],2,(LEN(RC[-1])-2))*1000000,MID(RC[-1],2,(LEN(RC[-1])-2))*1000000000)


    If Column 1 contains e.g. $2.06M then this formula would convert it to 2060000. This assumes there only two conditions ->B or M


    Just another option