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?



    • Sample.xls

      (13.82 kB, downloaded 105 times, last: )

    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 >_<

    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)