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:

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!

Re: Check If Something Is In Named Range

Could you be a little more specific. Is there a counta equivalent in vba?

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.

I was wonderin if anyone knew any tricky ways to get a column chart and a stacked column chart side by side on the same graph. Attached is what im tryin to do. If possible, i would like to move the green bar to the left of the stacked one. Thanks in advance for any help.

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.

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

Everything works great now. Thanks for all your help.

Re: Range Reference

Sure, here's a somewhat stripped down version of my file.

Re: macro to paste special value or format

Try something like

Sub Macro1()
    Selection.PasteSpecial Paste:=xlPasteValues
End Sub

you can then assign it to a keystroke (i use Ctrl-Shft-V)

Hope this helps

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

Still getting the same error:

Compile Error: Type Mismatch

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:

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:

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

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

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