Posts by Diego_Garcia

    B13 contains either N/A or a Date formatted as - Custom - D MMM. This month B13 is displaying a date - 04 Feb. The macro code is:

    Code
    If IsNumeric(Range("B13").Value) Then
        Range("B13").Copy
            Windows("Premier1").Activate
            Range("B12").PasteSpecial Paste:=xlValues, Operation:=xlPasteSpecialOperationAdd
        Else
            Range("B12").Copy
            Windows("Premier1").Activate
            Range("B12").PasteSpecial Paste:=xlValues, Operation:=xlPasteSpecialOperationAdd
            End If


    The macro runs the first line and then jumps to the Else. What I want it to do is Copy B13, the date, to B12 on Premier1 sheet. Where have I gone wrong please ?

    When B13 is blank (No value, no formula and formatted as General) this code still selects and copies, instead of selecting B12 and copying. Where is my error ?

    Code
    Windows("Premier Current.xls").Activate
         If IsNumeric(Range("B13").Value) Then
        Range("B13").Copy
            Windows("Premier1").Activate
            Range("B12").PasteSpecial Paste:=xlValues, Operation:=xlPasteSpecialOperationAdd
        Else
            Range("B12").Copy
            Windows("Premier1").Activate
            Range("B12").PasteSpecial Paste:=xlValues, Operation:=xlPasteSpecialOperationAdd
            End If

    Hi
    Can read simple macro keystroke vba and that is about it.
    I would like to put this function into vba
    if(isnumber(B13) then copy B13 (I can manage the paste).


    Also is this correct ?

    Code
    If Range("G12") <= Range("G1") Then
        Range("G12").Copy
        Range("B13").PasteSpecial xlPasteValues
        End If


    Tks
    Garcia

    Calculating imported Data Text Files


    Hi
    Presume this is how to do it. Two attachments.
    1. Rla01640 is the raw log, save it.
    2. Internet Usage.xls is my completed sheet.
    Format column A as date, your choice. Format columns B & C as Custom – Type – [h]:mm:ss Enter your formula in C6 – drag down to Row 950. Position cursor at A5.
    Clik Data - Get external data – Import textfile. Choose rla01640.txt. Wizard appears. Select “Fixed width” – Start import at row 2 – Clik Next.
    Delete the 1st and 3rd columns. Move the third column right one point (at 21).(This eliminates the comma).
    Clik – Next. Choose “Do not import” for the 1st column and the 3rd (2004,), clik – Finish. “Existing worksheet” with A5 selected should show. Clik – OK.
    My poor eyesight failed to spot the comma followed by a space in column B.
    Thanks a lot. Le saluda con fraternal afecto en el Señor.
    Garcia. Monk of Solius

    Solved ! When I imported the data, the wizard shows a preview. I told the wizard to import the date column but not to import the Year column (2004,[space]) followed by H:m:s. The date column does not display 1 as 01 so, when 10 July appears it places the comma and space after 2004 into the H:m:s column thus entering the data as Text. Solved by formatting the date column in Excel to show 2 numerals (01)

    Calculating imported Data Text Files


    Hi jindon


    Your parantheses do not match - 5 open 6 closing. Corrected but still get false instead of a blank cell and the paste stops at C194. The main problem now is being unable to drag past C194 without getting #value!

    Calculating imported Data Text Files


    Well, in Jindon’s first solution I modified it to
    =IF(MOD(ROW(A1),4)=1,B6-B5+IF(B6<B5,1))
    The inclusion of “IF” caters for times going through Midnight.
    However it returns “False” where there is no valid calculation (B7-B6).
    In Domenic’s solution it does not return any “False” but, in both solutions the last result is at C194, C196 et seq display the #value! error. The total hours up to C194 is 12:29:02. The next entry only adds 1:54:35 so why it should not display this I do not know. Also tried manually entering the formula in C194 and dragging down but still got the #value! error (against the “Value if true” equation). Grateful for any further ideas.
    Thank you

    I import a log of my internet usage times for a month, using ‘Data – Get external data – Import text file’. The data is start and end times, each range seperated by two blank cells, e.g. data is contained in B5, B6, B9, B10, B13, B14 et seq. C6, C10, C14 calculate the total time (B6-B5+if(B6<B5,1). How can I copy the formulae in C6 to C10 and every subsequent 4th cell down to C882, apart from the laborious individual pastes ? One answer would be to get the data to display on two rows, start times on row B, end times on row C. The formula on row C being copied the length of the row by dragging. The Import text wizard automatically places the data in a column.
    MS Office 2000

    Run Macro...


    Hi all


    Well there was an end statement and that was where it freezed, it had done the delete action correctly. Took the End Sub out and of course got "Expected End Sub", put it back in and Estupendo! everything ran from starter to finisher. Not used this Macro for nearly a year perhaps wanted a 'Dusty Off'. Sorry not got back quicker, watching the D-Day on TV.
    God Save the Queen.
    Thanks
    Diego

    Hi
    I have a macro which tidies up before it does some copy/paste. I want the macro "TransDataBase"to run then call another macro "DeleteRowOnCell", then finish running the first macro (TransDatabase). At present it stops after running "DeleteRowOnCell"


    Portion of "TransDatabase:
    Columns("j:j").Replace What:="L", Replacement:="", LookAt:=xlPart, _
    SearchOrder:=xlByColumns, MatchCase:=False
    Columns("j:j").Select
    Application.Run "PERSONAL.XLS!DeleteRowOnCell"
    Range("Data").Select
    Range("LastData").Activate
    Selection.Copy


    Pretty clueless on VBA. Tks

    I have a straight forward keystroke macro which opens a new bank balance worksheet (from a template) on the 5th of each month. One of the received credits occurs every 28 days on a Friday(row 11), cell B11 shows this date. B11 in the template has the number 28 already entered, using copy/paste special add, the B11 date from the Feb worksheet when pasted to the March worksheet shows a date 28 days forward. 6 Feb shows as 5 Mar when copied and pasted. 28 days forward from 5 Mar is 2 Apr, therefore both these dates show in the March balance worksheet (closes 5th April). The 2 Apr shows in B12, it is this cell that needs to be copied (not B11) next month. I need to add to the macro something like "if(isnumber(b12),Range("B12").PasteSpecial"...etc,(otherwise)"Range("B11").PasteSpecial"...etc If there is only one date showing B12 shows Text.

    When using the Help for "=IF" on the answer pane I selected the link "See Also". An error was returned
    "Object doesn't support this property or method URL: mk:@MSITStore:C:\Program'%'20Files\Microsoft%20Office\Office\1033\xlmain9.chm::/html/xlfctIF.htm.
    What is this ?