Posts by DrewBe

    Re: Convert Textbox Text Number to Numbers


    I have it working now. It turns out the problem wasn't assigning a numeric entry into an integer. The type mismatch occurred when there was a blank in the field. I fixed it by intializing the fields to have a 0 in them. Thanks for your help.

    I have a Userform where i have the user in put a dollar amount in a text box. Im having difficulty getting this assigned to a variable as an integer. I get a "type mismatch" error. I have tried declaring the variable as an integer, and tried the cint() function, but i get the same error. Can anyone help me out? Thanks in advance.

    Re: Check If Something Is In Named Range


    Sorry for the late post before. Your idea pointed me in the right direction.
    Based on what you sent, i am now using:


    Code
    If WorksheetFunction.CountIf(MedRng, Pl) > 0 Then MsgBox ("Med")
        If WorksheetFunction.CountIf(BalRng, Pl) > 0 Then MsgBox ("Bal")
        If WorksheetFunction.CountIf(OriRng, Pl) > 0 Then MsgBox ("Ori")


    eventually the Msgboxes will be replaced with functions, but i got to where i needed to now. Thanks for your help!

    I have a list of several ranges defined in vba, and i am trying to find which range a cell containing word is in excel. Im am looking for help for a good way of doing this.
    For an example: Say I have 2 ranges defined, the first range corresponds to column A, the second to column B (just an example). The word "Dog" is written in cell B15. I want to find out in vba what range "Dog" is in. I would like some vba code to check and find either if it in a given range (boolean) or a way of checking a list of ranges and returning which it is in. The word will only appear once on the spreadsheet. I have played around with the find command (A=range.find(Pl), then check if A exists) but i havent had any luck (I get an 'Object variable or With block variable not set) I tried searching for a while, but was having trouble finding something related to what i am trying to do.


    Any help would be appreciated.

    Re: Embedded formulas vs. Macro Loop


    Derk, the code works great. It has effectively the same runtime as having the formula in every cell, but still the advantage of the smaller file size. I ran it on a few of my data sets, comparing ur version to the other, and the results came back the exact same, for all 25k+ pts in each set.
    I was wondering, for my benefit, if you could explain how the match function operates, or the role of the 1/-1. Does this just choose direction of the search (up/down)? Also, does it choose the closest value in the range that is less than it? ex: if you have 9.9, and ur table has 9,10,11, how does it pick the range 9-10 as opposed to the 10-11? Is this a part of the match function, or something else im missing? Im also a little unclear on the role of the "n". It looks like this is in there so if the last entry in the range is selected, it wont compute a result because it doesnt have the other end of the range to interpolate between? If the data is outside the range, the current function just picks the end value of the table, which i would like to replicate (Do this by substituting Interp = YVals(n) for Interp = "Error"?).
    Once again, i appreciate all the help and i appologize for all my questions.

    Re: Embedded formulas vs. Macro Loop


    The cells call the interpolation UDF. The function takes a value (x) to approximate a corresponding value for (y), and a table range (that defines the system). The interpolation is linear and 1-D. I do not feel comfortable posting the code or mentioning too many specifics as it is not mine and there may be certain restirctions associated with it. The code does not specify the variable type, is there some sort of default that vba assumes? There is something that i couldnt quite understand that limits the type of one of the variables in the code. This variable, which ill refer to as var, is set to take the max of 1 and an operation, and forces it into an integer.

    Code
    var = Int(Application.Max(1,"operation"))


    Now this variable is used in each of the elseif statements, being multiplied by another integer, but again the code forces it into an Integer type ( Int(2*var) ). I thought this was unnecessary as an Int * Int is always an Int (right?) and so i removed the code forcing it into an Int the second time. When i did this and ran it, the code took even longer to run, even tho It had fewer operations to perform (i think). Any thoughts on any of this? I am starting to create a new function that will perform the interpolation differently, in hopes it will be more efficient. I may ask for additional help with this code, which i should be able to post for input. Thanks again.

    Re: Embedded formulas vs. Macro Loop


    The sheet kinda serves as a template, using data from the first tab in the worksheet. When new data comes in, we replace the data set on the first tab, and everything needs to be recalculated. I tried looking at the function being called, written by someone before i started, to see if i could make it more efficient. Just for clarity, could someone verify to me that ElseIf statements are only executed if the previous if statements are false. I think this is the case, i just wanted to make sure. Theres about 35 of these in a row and if each is checkin for each of the 25,000+ pts, it could prolly take some time. As always, thanks for the help.

    Im workin with files with lots of data (25000+ pts). There are cases where an interpolation function is used for each point. Previously, a call to the function was inserted as a formula into each cell, and the calculations would take considerable time and sometimes resulted in running out of memory and not finishing the task. I thought it may be more efficient to write a macro to loop thru each cell and call the function and put the resultant value in the cell. After tryin this, i find the run time is slower (~60% longer run time). Was I incorrect in thinking the macro would be quicker? The file sizes are 20% smaller, however im not sure this offsets the added runtime. Any thoughts/comments appreciated.


    Other notes:
    used manual calculation and screen updating off as well

    Re: Range Reference


    Back to the problem i had before, where it doesnt select the range in the second line of the loop. I get a Run Time Error ('1004'): Select method of range class failed. Again, i appreciate the help.

    Re: Range Reference


    Thanks for the reply. I was having trouble selecting the range on the line above the maxcell assignment, but I appreciate the help here as well. However, i am now getting a type mismatch error, and the .Max part of the line is being highlighted. I cannot step thru the code, as this error surfaces immediately. Any ideas?

    Im having difficulty referencing a range on another worksheet. I am tryin to find the max in a range of cells for later use. I keep getting Run Time error '1004'.


    Here's the code ive been messin around with:

    Code
    Private Sub Worksheet_Activate()
        Dim i As Integer, maxcell As Range
            For i = 1 To Sheets("Weekly Matches").Range("E1").Value
                Sheets("Weekly Matches").Activate
                Range(Cells(11 * i, 2), Cells(11 * i, 22)).Select
                maxcell = WorksheetFunction.Max(Range(Cells(i * 11, 1), Cells(i * 11, 22)))
                Sheets("Standings").Cells(28 + i, 4) = maxcell
                Sheets("Standings").Cells(28 + i, 3) = maxcell.Offset(-8, 0)
            Next i
    End Sub


    There are probably several other errors here, usually i can figure them out just from playin around, but currently i cannot get the range referenced at all. Thanks in advance for any help.

    Re: VBA : How to modify a chart?


    I have a questoins similar to this issue, but this was the closest i could find.


    Im trying to use vba to change the size of an individual chart in excel instead of every chart on the page. Im using a for loop that generates 2 plots for each loop, and i want to specify the sizes and locations of these charts. I tried using the loop counter to select the charts individually, such as:

    Code
    With Application.ChartObjects("Chart " & (2*i-1))


    but i get an error saying it doesnt support this method.


    Can anyone advise me of a way to do this? Another thought I had was by using something like With Application.("Way to select current/active chart"), but could not find anything in my searches that would allow this.


    Thanks in advance for any help

    Re: macro for searching


    A few quick questions for clarity


    Is the data always going to consist of letters and searching for g, or is this just to simplify representation?


    Quote

    we find g and it returns, f, c, d and e respectively


    Where exactly do you want the data returned to. New cell range, msgbox?
    Post sample of worksheet if it will help

    Re: Going Back to original cell after macro runs


    Nova-
    the idea you suggested will work, heres some code if you need it.


    Put the lines

    Code
    Dim RL As Long, CL As Integer
        RL = ActiveCell.Row
        CL = ActiveCell.Column


    at the top of your module, under the Sub line


    At the end, use the line

    Code
    Cells(RL, CL).Select


    to return to the original cell


    Hope this helps


    Didnt see Dave's code when i wrote this, his will work, less code