Posts by VBAnewbie

  • Boolean Property for "Number stored as text"?

    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?


    Thanks!

  • worksheets do not work on other pc

    Re: worksheets do not work on other pc


    Hi,


    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

  • worksheets do not work on other pc

    HI,


    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!


    Kim

  • when the range in the index() is a link

    Hi,


    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
    defined.


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


    What is the correct sytnax for this !!


    Any help is appreciated!!


    Thanks,
    Kim

  • Insert Row VBA

    Hi,


    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
    ActiveCell.EntireRow.Insert
    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?


    Thanks!


    Kim

  • Using macro to change a formula

    Hi,


    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" ..?


    Thanks!!


    Kim

  • Recalcuate a function

    Hi,


    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.


    Kim

  • import excel file into existing file

    Hi,
    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)


    Thanks,
    Kim