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 RepliesI have written a user type for my arrays but I am having trouble storing it. The number of arrays varies.
Dim arrTransactions() As Transaction
Dim assortedrowindex As Integer
For assortedrowindex = 2 To 100
Redim Preserve arrTransactions(0)
arrTransactions(0).CUSIP = Cells(assortedrowindex, 12)
arrTransactions(0).OrderDate = Cells(assortedrowindex, 9)
arrTransactions(0).BuyCurncy = Cells(assortedrownindex, 2)
arrTransactions(0).SelCuurncy = Cells(assortedrowindex, 10)
arrTransactions(0).Fund = Cells(assortedrowindex, 7)
arrTransactions(0).SettleDate = Cells(assortedrowindex, 10)
arrTransactions(0).BuyUnits = Cells(assortedtrowindex, 15)
arrTransactions(0).FxRate = Cells(assortedrowindex, 16)
I have an Excel worksheet that contains many data structures, each one consisting of an array of cells, say a3:z13, a14:z24, a15:z25 etc. I can code a custom object to easily access the data in the structure but only after I load the structure into the custom object in memory. What I'm looking for is a way to assign the structure to a custom object for accessing without having to load the custom object. Again I maybe headed in the wrong direction, it may be easier just to hard code it.
View 9 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 RelatedAs 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?
My 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.
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.
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..............................
I have 2 pivot tables in an Excel Sheet (Pivot A and Pivot B). They have different structures i.e. the table headings are different. I need to sum the "Grand Total" values from each of these pivots. Can anyone explain if the following is possibe:
1. Can you combine 2 pivots with different structures in order to acheive a single Grant Total value?
2. Can I create a formula which adds the 2 separate Grand Total Values?
I am retrieving a CSV file from the net. In this file there are 'x' amount of row data and 7 columns. I only care about the values in the 7th column for each row. I also don't care about the entire first row. A graphical version would be represented something like this, with the values I want colored in orange:
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
|---,---,---,---,---,---,---|
.
. extending until the end of the data set
.
I've managed to dice this thing into a jagged array by first splitting it using vbLf as a delimiter, and therefore adding those to an array called Lines(). Then I split Lines() up using commas as the delimiter and threw those into a jagged array, let's call it Breadcrumbs()(). I want to throw all the values from Breadcrumbs(i)(6) into an array of its own. Here's my code so far:
Code:
Public Sub CSVparser(file As String)
Dim Lines As Variant
Dim j As Integer
Lines = Split(file, vbLf)
ReDim breadCrumbs(UBound(Lines)) As Variant
For i = 1 to UBound(Lines) - 1
breadCrumbs(i) = Split(Lines(i), ",")
Next i
End Sub
I often find that it would be useful to have a data type for geographic coordinates (i.e. latitudes and longitudes) that would e.g. permit/facilitate formatting negative values as South and West, direct subtraction of one latitude/longitude from another, conversion of degrees-minutes-seconds to decimal degrees, properly formatted axis labels when using Excel to draw maps (at the moment, I am editing the axis labels in the PostScript file by hand, when I don't use chart labeller to paste axis labels in) and so on.
(Time formats could be used, if only they could be prevented from converting hours over 24 to days, which I have not found out how to do.)
Therefore: does anyone know of any Add-In out there somewhere that could supply the lack of a geographic coordinate data type?
I have a non-contiguous range of cells that contains Values, Formulas, Text or Dates. In some cases a field containing a value could instead contain a formula depending on who is filling out the form and/or what information is known. For example, a cell might contain a value, i.e. Utilities, and I might either know the total ($76,310), or I might need to perform a calculation right in the cell to obtain the total (=54236+9587+12487).
My goal is to be able to copy the information contained in this non-contiguous range to the right some 52 columns (this is a safe temporary storage location) and then be able to copy it back should I need to.
One other problem may be that the source cell might be a merged cell.
Since I cannot determine whether the information is going to be a value or a formula, and I have some text and date formats to copy as well, is there some way to copy this range and keep the data regardless of format?
My Range might look like: (F4, C5, D6:D7, D9, D13:D15, D17:D18, D22:D25, D27:D30)
F4 is a date field, C5 is a merged cell holding text, D6 is the Utilities field described above. The other fields are all formatted as dollars or percentages.
Though I am familiar with the use of Array in formulas I can't seem to get my head around the way arrays are used in the VB world.
It is my understanding that you can read and write data into an array for copying/moving it around and reordering.
This is a simplified version of what I am trying to do.
I have some data in a row, say:
1, 2, 3, 4, 5, 6, 7, 8 etc.
I would like to poplute an array and then "Write" the data into a range so that it comes out something like:
2, 4, 1, 3, 8, 6, 7, 5 etc
I have tried to play around with array in Vb but could not seem to get anywhere.
Am I mistaken that is is possiable?
If someone could point me in the wite direction I would appricate it.
I have searched for this online and have found examples that I either did not understand or was unable to adapt...
I am looking for code to do the following:
1. Copy data from Column A in Sheet1 (could be up to 1000 rows)
2. Paste the data in Sheet2 in "arrays" of 9 columns and 16 rows
So basically, the first table would contin the first 16x9 = 144 numbers that are being copied and pasted from Sheet1. The second table would include numbers 145 to 288 and so on.
I have formed data arrays in VBA after running a time series simulation model. The array is m simulations x n periods. Small example: ...
View 6 Replies View RelatedAny easy way to retrieve data from another table using an array formula.
I have two files that I am using that contain 2 sets of data with columns for name, address, city, and state. The red highlighted data needs be used to find the blue data first column, which is a possible name for the company found from the red data. The issue is that the blue data is larger and has rows of data that will not be found in the other table.
I have the spreadsheet attached. I attempted to use an if/and statement :
=IF(AND(D2=C8I:I,E2=J:J),H2,0)
but found out that it does not work with arrays and only found the first address by default.
How would I set up a formula to retrieve the possible names using criteria from the blue data such as if the address and city match, then input that company name?
I would like to select a item from a list and have a text box display data from the next column (corresponding row) Also, I would love if I could type something into a second textbox and have that copy onto a third column (again, corresponding row) Also, if the add comment command button could transfer that record to the "commentted items' sheet. I have attached an example.
View 2 Replies View RelatedI have code that runs through multiple arrays trying to match data to items in the arrays and it takes a long time to run.
Code:
dim a as long
dim b as long
dim c as long
dim d as long
dim e as long
dim MyAarray as variant
[Code] ..........
That's basically what the code does. however, it takes an extremely long time to get through with everything as each array increases in size.
I was trying to subtract two arrays of data and putting the result in some cells but with no success unfortunately. I'm relatively new to VBA and I'm just starting now to make calculations with arrays so excuse my little knowledge. The arrays that I'm trying to subtract are from row 1 to 250 and m and n variables have the number of the columns. Here is my routine:
View 3 Replies View RelatedI am trying to create/modify an XY scatterplot using VBA.
I declare local variables and point them to the chart and a new data series for the chart, such as:
Dim chartone As Chart
Dim chartseries As Series
Set chartone = ThisWorkbook.Charts("Chart1")
chartone.ChartType = xlXYScatterLines
Set chartseries = chartone.SeriesCollection.NewSeries()
I set the series data, using an array, such as:
chartseries.Values = Array(1, 3, 5, 7, 9, 11)
I can set the Xvalues to a set of dates by setting .Xvalues to a woksheet range that includes date-formatted data. (like this):
chartseries.XValues = Worksheets("sheet3").Range("m9:m14")
(where m9:m14) contains dates...
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 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.
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.
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 View Relatedinterpolating 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