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 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 RelatedHow to convert any types format into Text with 5 digits in selection?
For instance, the content I select is "234", and I want it to convert to "00234". Just like the function "TEXT" in excel. How can I realize it promptly in VBA?
I 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)
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..............................
In the attached excel spreadsheet, I want to get the result in "Actual" from the sheet "Data"
That means on sheet "Data", I want a macro which will transpose the data from column K to column O into a single column .
-->The macro will delete existing records in sheet "Result" and then start transposing.
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 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
I 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 RelatedIn 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?
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 Relatedto calculate how many days of each different leave type have been taken for each Cost Code within an Excel 2003 spreadsheet. What adds to the problem is that the data extracted from our system comes to +/- than 50 000 lines. So doing this manually is out of the question.
I have attached a spreadsheet containing an extract of the information (DATA Tab) to better understand. Under the Summary Tab I have the format in which the information must be presented. For each Cost Code/Department I need to calculate how many e.g. Annual Leave/Sick Leave/Other Leave etc. was processed for each month from January to end of October.
Has anyone dealt with something similar? I would really appreciate any assistance as this problem is way above my Excel skills (not that it’s actually above average in any case... ;-)
I have a huge file with names, addresses and account types & numbers. However, there's a separate row for each account type instead of listing the person's name once and indicating all of their account types on that row as well in separate columns. I've seen several post regarding different Macro codes to use; however, none work for my project since I have several columns that need to be compared beginning on A2:K2 to A24628:K24628. I have attached a small example for your review.
Transpose Needed.xlsx
I have the following macro that is linked to a button.
There is also a function that is called to check to see if the file is already open.
These macros & functions work properly.
Basically, the Case statements check the extension of the file to be opened and then calls the correct macro to open that particular file.
I have shown the macro that is called by the Case statement that has the extension .xls . . . Excel
I want to keep these Case statements as I always want Excel files opened in new instances of Excel.
What I need is when the existing case statements do not find an extension that is listed, I want code added to this macro that can open any type of file, such as files with other types of extensions or without extensions.
My son suggested that I should try to get a macro that could open the file using the RUN command found under the Start Button. Sounds good to me.
Basically my sheet links may appear as a cell in another tab or it might be to an http address... Here is my formula I have setup up right now.. Only problem is, no matter what I put into the logical test to get a "true" result for the http addresses it still says false..
View 2 Replies View RelatedI'm trying to set an array of types equal to a set of cells in excel, first I've defined an ORoom with attributes such as ( ORID, ORName,...), then ORooms whish is a set of ORs, from OR1 to OR 30.
Now I want to put ORooms equal to the whole range for them containing all information for all ORs, I'm trying this code:
Public Type ORoom
ORID As Integer
ORName As String
Cases As Integer
[Code] ........
But it gives me the error Invalid outside Procedure, referring to "A2:A31", I've checked but that the correct range for it.
I have something I've been trying to figure out for quite some time. I have to add the word 'special' at the end of various cells that has text already in it. Is there a macro that does this? Everytime I create one, it wipes out the text and adds the word 'special' instead. I need it to add the word 'special' at the END of the text that's already in there.
View 9 Replies View RelatedI 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)