Posts by VBAnewbie

    Re: Boolean Property for "Number stored as text"?

    I believe I also have a similar problem.

    I copied and pasted an online report onto a spreadsheet, and the numbers are stored in text format (used istext to verify that). However, I could not
    manipulate the number even after I attempted to convert them to numbers by using value().

    I attached a sample.xls.

    Any thoughts about how to convert the text?


    Re: worksheets do not work on other pc


    Here are what I have:

    Function PLCalc(inputvar As String, ddate As Date) As Double

    Dim start_date As Date
    PLCalc = 0
    start_date = "11 / 1 / 04"

    j = WorksheetFunction.Days360(start_date, ddate) + 1

    With ThisWorkbook.Worksheets("JD Entry")

    Select Case inputvar

    Case "sef_trader1_hub1"

    For i = 1 To 56
    temp = .Cells(219 + i, "AO").Value * .Cells(325 + j + 35 * (i - 1), "AH").Value
    PLCalc = PLCalc + temp
    Next i

    End Select
    End With
    End Function


    I have this problem which is really killing me. I have built a spreadsheet using some UDF. In the UDF, I use a with/end with, and when using the with statement, i specified the with workbooks(" ").worksheets("")

    When I tried to link the values (calculated by this UDF) to another spreadsheet, everything works. But when my manager tries it on his pc,
    he always got the $values everytime.

    I really dont understand why it works on my pc but not his. And I'm taking
    the blame along the process X(

    Any help is appreciated!



    I'm using an index function like this = index(A1, 1,1) where in Cell A1
    is the text Range1. Range1 is the name of the range of cells that I

    However, =index(A1,1,1) does not work! Nor = index(""&A1,1,1)!!

    What is the correct sytnax for this !!

    Any help is appreciated!!



    I have the following codes to insert 100 rows starting from the activecell.

    Sub AddRows()

    Dim i As Long
    Application.ScreenUpdating = False
    For i = 1 To 100
    Next i

    End Sub

    I saw that this macro took rather long time. What is a better way (without
    using the for loop) to insert 100 rows?




    I have a big range and within each cells I have something like
    =BV1 - sum(), BV2 - sum(), BV3- sum()

    The formula for these cells are okay, except that I need to change
    each to BK instead of BV ... and keep whatever in sum() the same

    What VBA codes can I write so I can go to each cells' formula, and just
    change the "V" to "K" ..?




    What can I do to recalcuate a function everytime I run a macro?
    The function I have has two inputs, but these inputs are independent of the macro. Thus, the value returned by the function does not change because
    the inputs do not change.

    I believe a line would do, something like "refresh function" or something?

    Thanks for any suggestions.


    I have the following problem. I hope someone is able to offer me help.

    When I want to import an existing file from a excel file stored in my computer, i use the following lines of codes:

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;Z:\Kim\Sept 2003\2003\home\ftp\pub\account\lmp\20030930.csv" _
    , Destination:=Range("A1"))

    The problem is, I have to import many files for each day, namely, 20030929
    ,20030928 ... How can I add a parameter variable into codes like this?
    So, I want something like this (but obviously this doesnt work)

    With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;Z:\Kim\Sept 2003\2003\home\ftp\pub\account\lmp\2003[date].csv" _
    , Destination:=Range("A1"))

    So, I just need to input "date" (0929, 0927...etc)