I want to have an array where each record contains a name and three numbers. My understanding is that Excel requires all element types within an array to be the same, and so I turned to a user-defined data type. But I don't seem to be able to create a dynamic array out of this type--when I try to ReDim Preserve it, I get an error saying that it's already defined. Can't I do that?
View 3 Replies
Does using ReDim to size an array also initialize all array elements to zero by default? If not, is there a quick way to autoinitialize all array elements to zero?
View 2 Replies View RelatedI iterate through a list and store the data in an array.
[Code] ..........
This works fine, but I tried to have 2 dimensions to the array and It no longer works!
Currently I am building a class to keep track of entries I have made during the macro execution. Thus far I have:
Private Type Memory
MemoryArray() As Variant
End Type
Private Sub Class_Initialize()
Redim MemoryArray(0) As Variant
End Sub
Public Property Let AddToMemory(Object As Variant)
'memory is empty
If UBound(MemoryArray) = 0 And MemoryArray(UBound(MemoryArray)) = "" Then
MemoryArray(0) = Object ..............
I have: ReDim X(3,1) as double. The code runs fine on my machine, but when I take it to another machine, the code gets stuck on that line.
View 3 Replies View RelatedI have an array that is going to have a variable length every time the macro is run. I set a really high length at the beginning and when I want to unload the data, I want to redim the array to only the amount of data that was loaded into the array.
I am getting an error of "Array already dimensioned" when use the redim function near the end of my code below.
Code:
Private Sub Trend_Click()Dim GraphRow As LongDim TodaysYear As Integer,
Adjustment As Integer, EndYear As Integer,
StartYear As IntegerDim X_Axis_Array(1 To 100) As VariantDim SelectedStmt As StringDim X_Axis_ArrayEnd As IntegerTodays
[Code] ........
I've written this function to re-dimension an array based on the size of the range it is storing. Originally all my variables were simply declared as:
Code:
Dim XLRecOutput() as Variant
I then wrote a function which took the variable and the range as follows:
Code:
Function LoadRangeToArray(dataArray() As Variant, selectedRange As Range, Optional blnLoadData As Boolean = True)
With selectedRange
ReDim dataArray(1 To .Rows.Count, 1 To .Columns.Count)
End With
If blnLoadData Then dataArray = selectedRange
LoadRangeToArray = dataArray
End Function
To call the function, it's simply:
Code:
XLRecOutput = LoadRangeToArray(XLRecOutput, Range("XLRecOutput"))
Now I've created a class and set up Get/Let properties for the variables instead, but the above function fails with a Compile error on the call - 'Type mismatch: array or user-defined type expected' and I can't get my head around how to resolve it.
I found quite a few posts about this problem, but none of the answers was any use to me. I need to redimension a 2 dimensional array in a Sub. I deleted all the code that is not of interest:
View 3 Replies View RelatedI'm trying to make a converter between about 8 various types of values. These are not units like Km or miles or something like that, but rather numbers that represent a specific "hardness value" on a variety of scales (to name a few: HRC, HRA, K)
What I've been doing so far is plotting the two types against eachother and then getting the best trendline I can so that I can use that formula to convert between the two with relative certainty. (for example, when plotting HV vs HRC my fourth order polynomial trendline with an Rsquared of 1 is y=0.0001x4 - 0.0188x3 + 1.0768x2 - 20.709x + 350.69)
My questions comes up where I was hoping to make a window or box of some sort allowing the user to input a numeric value, then selecting the Input units and the hopeful output calculated units, and have the box spit back to the user the conversion.
I want to be able to create a range of VBA userforms to quickly perform long tedious tasks. I want these userforms to be accessed from a nice tidy toolbar.
I have done this and it looks nice and works well. What I would like to be able to do is have my custom toolbar of userform controlled functions be transferable so that if someone else wants my toolbar and attached functions they can install it easily much the same way you can do with an add in.
Is this sort of thing possible or does it require them to manually install all my userforms, modules and toolbar? If it is possible what sort of things should I be looking at?
I have a dataset that has a good number of duplicates, many of which have more than 2 records.
In these sets of duplicates, there are two fields that have many combinations of values.
I would like to find a way to COUNT THE COMBINATIONS in all of the sets of duplicates, as in, every time there is a type of a certain combination of values, create a count for it:
12345 9 9
12345 9 0
123456 9 9
123456 9 0
(Total: 2) For 9/9 and 9/0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
12344567 0 0
(Total: 2) For 0/0 3x
I have attached a listing of types of combinations that I found by scanning the database, but I don't know what formula or functions would do the trick. I don't think there is something in Subtotal or the Count functionality that would apply but I will fumble around.
I need to find the total $ collected on repair orders that contain customer pay and warranty repairs. The problem is that C and W show up on differerent rows, if an RO has 4 lines 3 may be c and 1 line w. I don't know how to use pivit tables to get the info I am looking for, or if that is the best way to fnd my answer.
View 8 Replies View RelatedI have a problem concerning datatypes for an if-loop, the failure message tells me "Datatypes incompatible" for this code-line:
View 14 Replies View Relatedsome of the functions in this file, and the problem is that I have one bug I can't fix.
There are four sheets. The first two sheets contain different types of scores.
The fourth sheet ranks each of the different types of scores on both first sheets. The third sheet reports out on the bottom five scores in each category. If one of the scores is missing, the whole thing gets screwed up.
I have attached the file and removed a some of the scores to illustrate.
As a complete novice....I want to:
1. Create a list of 3 different types of projects
2. Write three different types of calculations that should be undertaken dependent on the type of project. Each will be in a separate worksheet.
3. Make sure once I've checked the project type I apply the right calculations that match the project type
4. Summarise the results of the calculations against each of the projects
Can I do this just in excel or do I need to use macros?
I want to sum the values of two userform text boxes however I assume they are stored as strings so I get 1 + 2 = 12 - how do you convert strings to integers in VBA? It seems as though it's different to VB where you'd just use convert.toInt16() etc
I have portion of a macro that works when I specify a range like this:
ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets(A).Range("O5:T41"), _ PlotBy:=xlColumnsEnd Sub
But I need one of the coordinates to be changeable on eacj round of the macro so I wrote this:
ActiveChart.PlotArea.Select ActiveChart.SetSourceData Source:=Sheets(A).Range(Cells(5, 15), Cells(B, 20)), _ PlotBy:=xlColumns
But when I executed the macro, it froze on this lines saying "Method of 'Cells' Global Failure. I need a way around this but I have no idea how at this point.
interpolating numbers in excel for an exponential function. I want to know how to be able to curve a curve in 4 different ways essentially the 2 that make up the two halves of a peak and the 2 that make up the two halves of a trough. I've tried using the LINEST function to create this interpolation and it's good to get 1 of the 4 curves I'm interested in. how to do the other 3? I'm also interested in knowing if these functions "whip the tail up" as in almost like how the tail end of a "sin" function flips back up. here it is.
[URL]
For this example of the exponential function, it creates the curve in one particular direction. I was wondering how I would do it to have it curve in another direction, for example I was testing the same function, but going downwards, and also having the tail curve back up (by hump I mean to simulate a gaussian distribution with the tails) how would I do that. seems like I now have part 1 of 4 aspects of the curve. here's the attached file to show what I mean. I want to get the hump going up and then sort of reflected along the y-axis and then similar features if I were to reflect the curves on the x axis. is it possible with the functions in excel? Linest test.xlsx
I am having trouble converting file formats. I would like to convert a.xlsx file to a .xls file. It is password protected and everything I have tried to use to convert the file has failed.
View 5 Replies View RelatedI would like to create a chart in vba which contains 2 sets of data, both using the same x axis. The first must be a line graph, the second a staggered bar chart. This must be represented on one chart with data labels. Also, how do I change the colour of the plots?
View 5 Replies View RelatedMy question is about creating User-Defined Data Types. Is there ANY way possible i can create a User-Defined Data Type that declares a variable of another User-Defined Data Type instead of the Pre-Defined User Types like String, Integer, etc?
The following explains my problem in more detail.
I know to create a User-Defined Data Type at the top of the module before any procedures. Like this:
My question is about creating a hierarchy of Data Types.
I need to code 3 Data Types:
1.) MealPlan
2.) Meal
3.) MealItem
MealPlan represents a daily meal plan. It should have a certain number of meals. An example of MealPlan would be "Healthy" and have a total of 3 meals. Meal represents a single meal. It should have a certain number of meal items. An example of Meal would be "Meal 1" and have a total of 3 meal items. MealItem represents a single meal item. It should have a certain number of protein calories, fat calories, carb calories. An example of MealItem would be "Broccoli" and have protein calories of .2, carb calories of .8, fat calories of .1.
By using Data Types, I hope to create meal plans, which are made up of meals, which are made of meal items. The following code creates a Data Type hierarchy for two Data Types. I've examined it closely but i'm still having a hard time recreating it for my needs as stated above.
I have three items I would like to graph, on one chart. The first is %Complete. This is a number figure. The next is the date the item is due to ship. Obv, this is a Date. The third is today's date. Basically, I would like to display in the same chart so that we can see how far a long a project is, in relation with today's date and how much longer we have until it needs to ship.
This is the current display of the chart:
Is there a way to show the ship date, as the 100% complete mark? In order to make it display half-way decent, I had to format the left y-axis to go above 100%. I first tried to format the right y-axis, but was extremely confused to setting values. I figured out that the end date is somewhere in the 40,000 value mark? WTF is this about? Is 1 equal to 24 hours? I assumed that was the case, but then when I pick the value it should end with, it doesn't seem to react that way. I want the right axis to start with 12/01/2011 and end with 5/01/2012. How can I achieve this?
I have a csv file. When I open it by double clicking, all the finance figures appear as currency with the currency symbol. I can use the SUM function on these and it's perfect.
However, when I open this file using a macro, these same numbers look the same, except that they are now left justified and are text because the SUM function no longer works. If I select a range, I get the count of the number of items selected showing at the bottom of the screen but not the sum.
I have tried opening the file using the OpenText function which has several ways of setting a date format to the fields but nothing for numbers.
I have even tried, in the macro, selecting a cell with numeric 1 and then selecting the range and pasting the 1 as a multiply function but this doesn't work either.
I an newbie at VBA but I took some short programing classes back at my college days. I am trying to declare an array with different data types and since that seems to be imposible for what I gather then my other option is to declare what I remember as a structure.
View 9 Replies View RelatedI have a procedure that was working perfectly yesterday. I've not changed it and yet today it doesn't seem to be working.
I keep getting a 'Runtime Error '13', Type Mismatch' debug error.
Public intRowCount As Integer
Sub mcrCopyDataNewSheet()
Dim strRawDataBook As String
Dim strNewBook As String
Dim strRawDataRange As String
intRowCount = Range(Range("B5"), Range("B5").End(xlDown)).Count
strRawDataBook = ActiveWorkbook.Name
strRawDataRange = Application.InputBox(Prompt:="Please enter the Raw Data Range beginning with Earliest Start on Sunday and ending with the Saturday Rota:", Default:="L:AR", Type:=8)
In one excel book I have 2 sheets.
1. One sheet (request type wise) contains request type and the phase which it belongs to. eg request type A belongs to phase 1 and request type B belongs to phase 2. (like wise there are 212 request types divided into 8 phases)
2. The second sheet contains raw data (request type) where the request types are randomly arranged.
What I am trying to do is:
Compare the raw data of sheet 2 with standard data in sheet 1 and paste the phase to which it belongs to with a help of macro/code, since the standard data (212 request types with 8 phases) is huge.
to create a macro that counts a number of values for a couple of types. The list looks like this;
29 bananas
13 apples
18 bananas
14 pears
7 pears
etc.
So i want to create a loop the goes through the list and adds up all bananas, apples and pears. How do I do that in the best way?
Putting the final touches on a project I have and the last element doesn't work 100%. the scenario: I have a function that I wrote based on the user's input of the desired column to perform the operation. As a method of error-trapping and ease of use, I set instructions for the code to activate:
1) if the user highlights multiple cells of the desired column
2) enters the letter of the column (up to "z", not case sensitive)
3) enters the number of the column
Sub Button5_Click()
Dim ColumnUsed As Variant
' find number of columns selected
For MyColNum = 1 To Selection.Columns.Count
Cols = Cols + 1
Next MyColNum
'find number of rows selected
For MyRowNum = 1 To Selection.Rows.Count
Rows = Rows + 1
Next MyRowNum..............................
How do you convert all the picture files in a worksheet so they can be processed the same? Half my pictures have eight white adjustment circles framing them when they are selected, the other half have four blue circles and four blue squares.
View 2 Replies View Related
