Posts by Robert B

  • Tying Two Documents Together

    Re: Tying Two Documents Together


    Hi


    Welcome to the forum.


    In the completely different document, select the cell you wish to be completed and simply enter = (this will allow the selection of another cell in another workbook) then select the source cell, then press Enter.


    HTH


    Robert

  • Calculating Difference Between Two Times

    Re: Calculating Difference Between Two Times


    Hi


    I suspect that even with the Column width set at 255 all you will see will be #####..


    You could try a conditional formula like


    =IF(A2>A1,-(A1-A2),(A1-A2)


    which removes the problem of the ###s but shows a positive solution whereas the result is negative.


    The only way I can think of to get around this would be by VBA


    Code
    If Range("A2") < Range("A1") Then
Range("A3").Formula = Range("A1").Value - Range("A2").Value
Else
Range("A3").Formula = -(Range("A1").Value - Range("A2").Value)
Range("A3").Interior.ColorIndex = 3
End If


    which still shows a positive value but highlights the cell in red, (I think)
    HTH


    Robert

  • Last Used Cell

    Re: Last Used Cell


    Hi


    this will identify the last used cell


    Code
    LastCell = ActiveCell.SpecialCells(xlCellTypeLastCell)


    The Row & Column numbers associated with the last cell can be found by


    Code
    LastRow = LastCell.Row
LastCol = LastCell.Column


    Each assumes that the variables have been properly declared.


    HTH


    Robert

  • Now-time Changes In All Cells

    Re: Now-time Changes In All Cells


    Hi


    if you wish to have the value rather than the variable each time the NOW function is entered you could include

    Code
    selection.Copy
    selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False


    after the VB which creates the function


    HTH


    Robert

  • Automatic Sort After Filter

    Re: Automatic Sort After Filter


    Hi


    You could either sort the data before the filter is executed, or use Adavanced Filter, writing the filtered data to a new worksheet and sorting that.


    HTH


    Robert

  • Pivot Table Columns In Month Order

    Re: Pivot Table Columns In Month Order


    Hi Rich


    thanks for that, I don't have the book you mention but it sounds worth a try.


    I eventually, this morning, solved the problem by creating a custom list of the month sin the order I needed and added a bit to the macro which sort sthe columns according to the custom list.


    Thanks again for your help


    Regards


    Robert

  • Code Error

    Re: Code Error


    Hi


    I assume your query will always return something even if it is Null, so try


    Code
    Dim Last_Row As Long 
        Last_Row = Range("A65535").End(xlUp).Row 
If LastRow =<1 Then 
Goto 10 
End If
    Range("I2:BU2").AutoFill Destination:=Range("I2:BU" & Last_Row), Type:=xlFillDefault 


10


    if this is not the case then try



    Robert

  • Time To String

    Re: Time To String


    Hi


    generally, if an ' is placed before a numeric value the entry is rendered as a text string. Maybe this could be applied to your line of code


    Code
    F = d & ":" & Format(e)


    so that it becomes


    Code
    F = "'" & d & ":" & Format(e)


    although I am not sure how the next line of your code would react.


    Robert

  • Pivot Table Columns In Month Order

    Re: Pivot Table Columns In Month Order


    Hi Shades


    Its all done new each month with a macro which imports data from my accounts package. This data has a date in dd/mm/yyyy format, which the macro converts to Month, and it is this that is the column heading.


    Robert

  • Pivot Table Columns In Month Order

    Hi All


    I have a couple of Pivot Tables that I create each month summarising my Income and Expenditure. The only problem I have is making the columns appear in the correct order, i.e. where July is month1 through June(the following year) being month 12 to coincide with my financial year. Oddly enough last month (January) was fine, following December. This month January and February appear in columns 1 & 2.


    Anybody got any suggestions


    Thanks


    Robert

  • Pivot Tables With Vba

    Re: Pivot Tables With Vba


    Hi


    you might find it more flexible to use VBA in the form below, rather than a recorded macro. This code will create a Pivot Table based on the data in the ActiveSheet, given that you identify the sources of the Rows,Column, Pages and Data.



    Each time this is run a new Pivot will be created in a new Sheet


    If you were to post your workbook containing the data I could fill in the headings etc.


    HTH


    Robert

  • Code Error

    Re: Code Error


    Hi


    not quite sure what you mean by the Dim function not performing. Dim is simply a way of declaring a variable. Do you mean that you don't want the LastRow variable to be set, and that the subsequent code should not run if LastRow is Null or zero?


    Robert

  • Place Username In Directory Path

    Re: Place Username In Directory Path


    Hi


    if I understand you correctly, replace


    Code
    "C:\Documents and Settings\XXXXXX\My Documents\Salary Review Data 2007 - " & country & ".xls" _


    with


    Code
    "C:\Documents and Settings\" & User & "\My Documents\Salary Review Data 2007 - " & country & ".xls" _


    Robert