I need to make a fork in my code based on the type of data received from an input box launched from a right click and passed via the actioncontrol parameter.
The input box box is a range selector.
Dim seriesIdArray As Variant
seriesIdArray = Range(CommandBars.ActionControl.Parameter)
Generally, the user will have selected multiple cells as their range and I loop through using:
For j = 0 to ubound(seriesIdArray, 2)
However, if they only select one cell, I am getting back the value of that string in seriedIdArray, and that gives me a type mismatch error. I'll need to handle this a little differently, and I know how to do that part, I just don't know when I need to do this.
How can I tell whether they have selected one cell or multiple cells based on the value of the actioncontrol parameter?
I considered trapping the error type (13) and branching based on that, but then I end up with spaghetti code and I'm trying to avoid that.
I think I may need to create another more specific variable to take the action control parameter, test it, and then decide whether I should use an array or a range, but that's just a suspicion.
which i am using to map columns between spreadsheets. The basic numeric entries refer to columns to copy. But I want to make the routine smarter with the strings.
If array(x) = 12 (For example) Then Do something End If If left(array(x),1) = "=" Then Do something Else End If If array(x) Is String literal (e.g "xyz" Or "123") Then Do a third thing End If
i cant find a typeof or isstring kind of function. Isnumeric works ok for some values but quoted numbers (eg "123") return true (which isnt what i want). I have tried the left(string,1) = """ but excel seems to hide the quotes.
I'm trying to use Solver to determine an input parameter of the BINOMDIST() formula that provides the exact answer I want. It works for some inputs and outputs but not for all.
For example, I want 4 successes out of 7 trials to yield a 95% answer, meaning I need to itterate on the "P" input in the formula [=BINOMDIST(4,7,"P",TRUE) should result in .95]. Using solver it iterates successfully to .3413.
Now, change 4 to 6, and solver drives "P" to an answer that causes a #NUM result (P= 4.023), however I can manually input 0.6518 and I get the right answer.
I've tried adding constraints that won't let the input go outside the bounds of 0 and 1.0 but it does anyway.
How do I determine a cell's type (e.g., numeric or string or formula) and if numeric, the format (e.g., currency, general, scientific) including the number of decimal places.
Is there a function that returns the object's properties? Must I use isnumeric, isformula, and so on or is there one function that returns this information?
I though I could do this with a nested IF statement but it is too cunfusing for me. What I am trying to accomplish is this:
Experiment Is Steward EU ID Location Data Quality GE Entry Order
[Code] ........
I want to have a screen pop-up asking me what my limit < would be for column "ESTCNT" so if I put in 25 or any other number that it would highlight all the rows that are less than 25, then look at the row above and below and if it matches the same number (that is in the cell "Range" of the highlighted column) in column "Range" then copy that row to a new sheet. Meaning all tha rows that match the "Range" would be in the same new sheet.
The rows might be different lengths and that there will not always be a number in cell "ESTCNT". Column headers will always be the same but might not be in the same column each time. And if it is not to hard once it is completed to find column "SPPLOT" in the new sheet created and asking what I want to autofil the column with.
I'm having problems with date parameter for a web connection. I can provide the parameter and type the parameter via message box ... but when I want to reference a cell the query errors out.
i have five values as : 127 134 143 158 170 how can i list is in column for more than on time as 127 134 143 158 170 127 134 143 158 170 127 134 143 158 170 and so on
So basically I'd like to know if there is a duplicate line item what line item the duplicate actually exists on. Is there an easy way to do this with a formula?
Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.
1. Is there a VBA Function equivalent to the FIND() function, If so What is it? 2. Let's say Im Putting a Date into a inputbox, what is the type # for date (Type:=?)??
I am trying to fine an easy way to sort the attached spread sheet. it is a down load from our supplier which has a lot af data that i am not interesed in the only ones i need are columns description (r) and units (o) and name (g).
I am wanting to split each waste i.e oil fitters, acid batteries etc and each site so that the total amount each site has returned can be tabled ,so that i can create graphs showing who has/ has not return their waste steams.
i have a file which consist of sheets called june 2007, color, and report. When i click on the command button on sheet june 2007 the code works and copies some data into sheet called report in that case some of them white color copied over and some of them blue.
Since at the actual file there are nearly 500 rows and 32 columns when the report works, i want to sort each colors individually by looking the values at column called ETA (column "E")
white rows should be sorted in ascending order in column e wise blue rows also should be sorted in ascending order in column e wise each colors should be sorted individually
I am trying to figure out a way in excel to graph some data I have in a new, sophisticated type of chart. My data is new square foot sales divided by new square feet (for a retail store). It is a dollar amount. I also have this data as a percentage change year over year. If I want to compare two stores with different data -- what would be the best way to present it on a chart. Is there a way to show this on a 3-D plane so that both companies have the data in the same chart up against each other?
I have a user form with textboxes on it, in some text boxes, text should be entered, but in some number should be entered, and in some date is required, but How can control the entry, if some one type text instead of number, then it should not be accepted. NOTE: Before I have seen such example here, that uses the KeyPress event.
How can I identify a cell type at excel sheet (w/ VBA command) ? I find this command (from Access forum): http://www.thescripts.com/forum/thread601180.html but its working only with Access...
WHEN I FILTER FOR WORDS BEGGINING WITH LETTER I. THERE IS NO DATA...SO I HAVE SOME CODE THAT WILL POP UP MSG BOX. AND IF THERE IS THEN COPY DESIGANTED RANGE..HOWEVEER WHEN I FILTER USING THE BELOW CODE...IT DOES NOT RECOGNIZE THAT THERE IS NOTHING AND JUST GOES THROUGH AS NORMAL..
Sub START() Dim rng As Range Dim rng2 As Range Dim worksheet1 As Worksheet Set worksheet1 = worksheets("MAIN") Selection.AutoFilter Field:=4, Criteria1:="=I*", Operator:=xlAnd With ActiveSheet.AutoFilter.Range On Error Resume Next Set rng2 = .Offset(0, 18).Resize(.Rows.Count - 1, 1) .SpecialCells (xlCellTypeVisible) On Error Goto 0 End With If rng2 Is Nothing Then MsgBox "No data to copy" Else worksheets("noms").Cells.Clear Set rng = ActiveSheet.AutoFilter.Range rng.Offset(1, 0).Resize(rng.Rows.Count - 1).Copy _ destination:=worksheets("NOMS").Range("A1") End Sub
I'm trying to use application.match() but I'm running into a issue where I don't know if I'll be matching a string or long data type...
Here's the snippet of code:
Code: ' UNIT DATA ' Set Unit# s1 = ActiveSheet.Pictures(Application.Caller).Name iLen = Len(s1) s1 = Mid(s1, 2, iLen - 2) ' Set Sel Ex Work Date iCheck = Application.Match("MACHINE_NUMBER", Sheets("Allocation").Columns(1), 0)
[code]....
How can I get application.match to look for strings if s1 = "ABC123" or numbers if s1 = "123456"
I've tried dim variant and s1 + 0... But, haven't come up with a solution.
The error is: Method 'Range' of object '_Global' failed Line it fails on:
Set r = Range("myRange")
Sub LearnCells() Dim r As Range Dim n As Long Set r = Range("myRange") For n = 1 To r.Rows.Count If r.Cells(n, 1) = r.Cells(n + 1, 1) Then MsgBox "Duplicate data in " & r.Cells(n + 1, 1).Address End If Next n
End Sub Questions:
1) 1st Dim statement, is this valid? I still get a little unsure- in the data type lists in the help file, the list file does not list things like Workbook, Worksheet Range. 2) Why is the error ocurring?
I have a UserForm function which accepts as a string the name of a TextBox control on the form. The function returns a reference to the named TextBox control (or Nothing if the control does not exist). I have the function header defined this way:
Public Function TextBox(byval strName as String) as TextBox
The function returns a reference to the TextBox control like this:
set TextBox = Me.Controls.Item(strName)
(I know, the .Item is not required.)
The code which calls the above function first declares a TextBox object with this Dim statement:
Dim txtTextBox as TextBox
The routine then goes on to call the TextBox function like this:
set txtTextBox = TextBox(strTextBoxName)
When run, the above statement genrates a Type Mismatch error. I'm confused, because if I 'TypeName()' the TextBox function's returned value, it's "TextBox,".
I thought I could work around this problem by changing the TextBox function's return value's data type to Variant, but that produced the same result.
The only "solution" I've come up with is changing the TextBox function's return value's type (and any reference to the functions' return value) to Control.
I expected that the Controls object would behave similar to a Collection object in that it's items can be different types (TextBox, Label, CommandButton, ListBox, etc.) and no Type Mistmatch erros occur so long as the type of the item returned matches the type of the variable referencing that item. But is seems that Controls requires that any reference to one of its items must be type Control, not the actual type of the item returned.
I'd prefer not to use Control data rypes in my applcation, as that would require additional code in all of my subs and functions to ensure that any Control object passed to it is the correct type of control (TextBox, Label, etc.).
Can anyone explain what's going on here? Why shouldn't I be able to assign a TextBox type variable to Controls.Item("xyz"), so long as the item returned by Controls.Item("xyz") is type TextBox?
I have created a workbook for a co-worker. The way it works is data comes from an outside source with customer names in Column A and their Account Reps in Column H. This data is pasted into a template that I created with a very simple macro recording that filters on each Account Rep and is copied and pasted each into a different workbook. Then I had to go through each workbook (because of the info being sensitive) and create a macro that deletes the unwanted data because when the data is pasted it transfers all of the data and you can unfilter to see the full sheet.
I know there is a better way to extract data on each change in column H into a new workbook. Does anyone know how to do that? Another issue is the original data is not very clean so there are blank rows in the list. For Example:
I have a big array "DataArray" and want to access it:
For i = 1 To 4 variable = DataArray(SourceArray(i)) Next i
"DataArray" has two dimensions, so SourceArray has to consist of data like this:
SourceArray(1) = 1,2 sourceArray(2) = 2,4 etc
What data type does Sourcearray have to be? Integer doesnt seem to work, and DataArray doesnt like a string as coordinates. I have a workaround with two different arrays of integer for x and y coordinates, but this cannot be it.
My colleague and I are writing a udf which uses a "match/ index" combination to return a value to a cell from a named range in another sheet. The cell returns returns the error "value used in the formula is of the wrong data type". When we break down the code into a function which refers directly to the relevant cells, and a sub which returns the value in a message box, it works correctly: here's the code for the function and test sub:
Function MRL(PropertyType, ReturnType, Location, Year2) ....
I have a spreadhseet that has rows with headers, blanks & total lines throughout that need to be deleted. There are also about 48 rows of data at the bottom that are not needed.
The row numbers will be variable each time the report is run. I need the macro to find the last row of data in column A, use this as the last row in the range and delete all rows above this unless there is numbers 1 to 12 in column J.
DETERMINING ROW NUMBER FOR DATA FOUND WITH VLOOKUP IN VBA
I have a user form with a combobox (called STKCODE) that calls up a vlookup routine to search for this item in a named range on a worksheet and retrieve related data to display on the userform. All this works fine.
What I would like to know is how to easily determine the row number on the worksheet, representing the row the data is stored in, if thats at all possible....
