Posts by Diego_Garcia

Re: Checking for a number in a cell

¡Estupendo! Gracias Dennis. Working now

Diego of Solius

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:

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 ?

Re: VBA IsNumeric selects blank cell

Hola Batman

Estupendo. Splendid this now goes to the Else statement.

Thank you

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 ?

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

Solved Re: VBA for ISNUMBER function

Hi

Thank you for that. I will try them in the light of day.

Garcia of Solius

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 ?

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)

I tried that from Help, it displays 0, clear the contents and copy B194 to B198, still get #value! error, however the istext(b194) returns FALSE.

Quote

Jindon

I would not have believed this. b198 is True (Text). b194 is False. Yet b198 was copied from the same scource as b194 and is formatted as custom type h:mm:ss
No matter what I do it remains as Text. Any ideas on this ?

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

Sorry. I thought the function Type was available in VB. I shall stick with IsNumeric.
Tks

I want to use Type to find the value in cell A12, with an IF statement. I need something like "If Type (A12)= 1 Then"...etc. What is the syntax for this ?

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.

Tks xlite

I understand the IF formula, wanted to check the form for if(and or etc

It is the error that I am trying to eliminate

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 ?