Posts by mzp

    Re: Copy value and format xls to winword


    Just tried your suggestion and no luck:

    Function TCAdd(column As String)
        TAdd ActiveSheet.Cells(row, ActiveSheet.Columns(column).column)
        Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
            , Transpose:=False
    End Function

    The actual figures are being copied across but none of the formats (£, %) etc.


    I have set up a procedure to copy the contents of cells and paste them into a table within Word. The problem I have is that the routine copies the actual value but not the associated formate, therefore what displays as £1000.00 in Excel appears as 1000 in Word and what appears as 11.25% in Excel appears as 0.1125 in Word.

    How can I make the routine copy both the cell value and its format and paste into Word?

    The function I have written to paste into Word is:

    'Add a the column value of the current row to the table
    Function TCAdd(column As String)
        TAdd ActiveSheet.Cells(row, ActiveSheet.Columns(column).column)
    End Function

    The method for copying is as follows:

        While ActiveSheet.Cells(row, 3) <> ""
            TCAdd "C"
            TCAdd "E"
            TAdd ""
            TAdd ""

    Re: Loop to copy and group headings

    Hi Norie,

    I've uploaded a snap shot of the data to my original message. The original data file is a couple of MB. As you will see its the various headings in the example data that need to be grouped under the new headings and the various totals added up as well.


    Re: Loop to copy and group headings

    thanks for the suggestion Neil but I have to provide the data on a separate sheet in the cell range specified as thats the template I have been given.

    Hi...struggling to get going on this task:

    I have a series of headings in the range B5:B31 with values in D5:D31. What has to happen is the entries in B5:B31 need to be grouped under the headings listed in the attached sheet, the individual totals need to be grouped under the new headings and the new heading and totals then pasted to a new sheet with the headings starting in B9 and the corresponding total in cell G9.

    The new headings should therefore occupy the range B9:B21.

    Any ideas on the simplest way to achieve this task?


    Struggling with this,

    I want an input where the user enters in a date e.g todays date, then I want to check the range of cells H2 to H50 against the date entered into the input box, if the date in the cell is two weeks after the date in the input box then the cell interior is to be red and the font white.

    However, I only want the dates that are exactly two weeks ahead of the date to be highlighted, for example I do not care about the dates 6wks ahead etc. Further, the range of cells have txt entries and some are blank, I want the loop to skip these cells.

    Any help much appreciated


    Hello all,

    I am trying to create a VBA routine that will check the dates in column G, if those dates are within two weeks from Today then for the row in which that date appears to be filled RED and the font YELLOW.

    The Range of cells containing my data is A1: I26

    Column G contains dates and text, I want the routine to skip any cells in Column G which contain text.

    Finally, I want a seperate routine that will allow me to remove just the formatting which the first routine my have inserted.

    Any suggestions much appreciated


    Re: Sum IF Year changes

    Thanks but I want to totalfor the year so even if I have only start in Sept 05, I want to add the values for the remaining months in the year 2005.

    How can I amend the formula?

    I have a column of data that has be broken down into quarters:
    Mar-05 £10
    Jun-05 £20
    Sep-05 £30
    Dec-05 £40
    Mar-06 £50
    Jun-06 £60
    Sep-06 £70
    Dec-06 £80
    Mar-07 £90
    Jun-07 £100
    Sep-07 £110
    Dec-07 £120

    I then have another table by Years:


    What I want to do is to add up the values for the quarters which fall within each year, therefore with the example above the total for 2005 should be 100, for 2006 it should be 260.

    I think I should be using a SUMIF but can't get it to work.

    Does anyone have any ideas?


    Re: IF and Error Type Formula

    Thanks for the suggestion but I'm having no luck. The cells G4,J4,M4,P4,S4,V4 will be filled in gradually as figures become available hence why some of them say #Div/o at the moment as theres nothing to divide by. Effectively, I'm trying to get a running total going. Any suggestions on how to get around this?


    Re: IF and Error Type Formula

    Great, this works, thank you.

    Can you also suggest a solution to the following:

    I am trying to add up the following cells: =SUM(G4,J4,M4,P4,S4,V4). G4 and J4 have values, the remaining have the error #DIV/0 because they are meant to be percentages but a value is missing.

    What formula will add the cells with a value in it and ignore the ones with #DIV/0?


    I have a formula H6/F6, this is giving me the error #Div/0

    I have therefore written the following formula =IF(ERROR.TYPE(H6/F6=2),0,H6/F6). This works in that when #Div/O occurs I get the value 0. HOWEVER, when you insert a value into H6, I end up with #N/A appearing.

    How can I make it actually do the calculation H6/F6 (which should be 10 divided by 40)?

    Any help much appreciated

    I have a sheet called "top250", starting in cell A2, in cell B2 is a company name, this name could be repeated 3,4,5 times in the cells below, what I want to happen is that the value in column B on a sheet called "Relationship" is brought across and pasted into column A in the sheet called "top250" where the company names match. The company name on the sheet called "relationship" is didsplayed in column D.

    The values in the sheet called "Relationship" start from cell B9. The values go down to row 258. On the sheet called "top250" the values go down to row 1801.

    Any suggestions...I'm thinking this is a IF and a Loop but am getting hopelessly lost?



    I hope someone can help with this IF/Date and Offset formula (or suggest a better method).

    From H149 to L149 I have a series of Years (2006,2007,2008,2009,2010)

    From H156 to L156 I have a series of values derived from a formula (0, 250, 250, 144, 0)

    In cell BX 157 I have a date for example 01/09/2007

    I need an IF statement that says if the date in cell H149 is equal to or after the date in cell BX157 then the value to show is the value in row 156 but offset by 3 columns. So for example if I was going to display a value for the year 2009, then the value I would want to display would be 0 using the examples I have outlined above.

    Any help greatly appreciated


    Re: IF Statement + Maximum value if TRUE


    Thanks for your help, your formula got me started...the final formula I had to use was:



    I have an IF statement in I154 (=IF($BV$152<I149,$AR$150,0), this statement is copied across the range I154: AF154. The value if the IF statement is true comes from the figure in AR150.

    What I want to happen is that if the test is TRUE then the value displayed must not exceed the total displayed in G152. So for example the value if true (from cell AR150) is 250 but the Total (from cell G152) is 460 therefore I want cell I154 to show 250, then J154 to show 210 and from K154 onwards for it to be 0 because the total of 460 has been allocated.

    The total in G152 will change from time to time, the essence of the formula I am trying to achieve is that the True value displayed cannot exceed the total in G152.

    Can anyone help?


    Re: Run Goal Seek on change in another cell


    The calculation is set to Automatic, I have tried your suggestion of changing it to Manual but the only effect that has is that I then have to use F9 to update the answer in D25 and I still need to go via Tools etc to re run the Goal Seek.

    The calculation I think will have to remain on Automatic, I think I will need a VBA routine to kick in any time D16/17 is updated.



    What I would like to achieve is that when I change the values in D16 and/or D17 then a Goal Seek is automatically run that will have the Set cell as D25 to a Value of 0, By Changing D21.

    At the moment when I make a change in my input cells (D16 & 17) I'm having to manually run the Goal Seek.

    Can anyone suggest a VBA routine that will run the Goal Seek as soon as I press Enter on any changes to D16 and/or D17?

    Any suggestions much appreaciated