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 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 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)
how to do is the assignment of GoodArray1 to CurrentArrayToUse, i.e. "CurrentArrayToUse = GoodArray1" below:
Global NextArrayToUse() Global CurrentArrayToUse() Global PreviousArrayToErase() Global GoodArray1(), GoodArray2(), [etc] Global CurrentGuessNumber As Integer
[bunch of code, part of which assigns a number to CurrentGuessNumber, then the following...]
Select Case CurrentGuessNumber Case 1 CurrentArrayToUse = GoodArray1 NextArrayToUse = GoodArray2 Case 2 CurrentArrayToUse = GoodArray2 NextArrayToUse = GoodArray3 PreviousArrayToErase = GoodArray1 ReDim PreviousArrayToErase(0, 0)
I have created a userform with 3 textboxes and I want to return the values entered to variables for use in a procedure. I cannot figure out how to return the values.
Behind the command button I am unloading the form so that the calling procedure continues executing but my vars cannot see the controls.
I have tried making them public outside of the calling sub, but nothing. Can anyone point out to me what I'm doing wrong?
Split from Copy, Cut, Fill Relative Or Absolute Formulas
Originally Posted by AAE Welcome to Ozgrid.
Assume the following: Cell A1: is the input cell Cell B1: contains a number Cell C1: formula = A1*B1 or $A$1*$B$1
Your formulas must contains absolute cell references, other wise Excel will attempt to update them to the new cell references when copying/pasting. If you "cut", rather than copy, Excel will maintain the original cell references in the formula.
Both of these questions are well within the experience level of a user with above average skill level as your profile shows. Please lower it (before one of the Admins does it for you) to a more realistic level.
See this training page on how to toggle between relative/absolute cell references: [url]
Well cutting is a nifty trick, thank you for that, I don't believe you quite understood my first question.
Lets use my calculator for instance. Using something it has designated K (for no apparent reason) I can enter a formula, such as K=*2-4, and after that is entered every number I then type and press equals to it will run that formula and spit out an answer. (after entering that function, if I press "5" "equals" it will give me "6") Now what I'm wondering is if excel has a "K" function that I can assign to certain cells, namely a column, so that whenever a figure is typed in any of the cells in that column, the required operation is done, namely *.9685.
Back to coding in VBA after long months of Matlab, and banging my head around this simple code snippet:
Sub openfile(FilePath) Dim myWBk As New Workbook 'Now open Form myWbk=Workbooks.Open(FilePath) End Sub
This returns a nasty error "Activex Component couldn't create object". It opens the file given by FilePath though. What I would like is to assign the open workbook to the myWbk variable, such that it is easier to work with.
I'd like to assign a value located in a cell in a workbook to a variable in a VBA program in another workbook. I do NOT want to open the workbook that contains the cell with the value. I can't get the hang of referencing an external cell reference. Let's say there's a value in a cell named "nbr" in a workbook named "tst.xls" located in a path "c: est" that I want to assign to a variable "tstnbr" in a VBA program, what is the best way to code that?
Need exact VBA code syntax to assign a workbook (to be closed) sheet's cells J4 to J72 values to a variable called "ColJValues" to be assigned to another sheet (to be opened later in the macro). The values are all dates. Once the other workbook is opened later in the macro, need the exact syntax to assign the value in the above variable, "ColJValues", to it's cells J4 to J72.
I have set up a number of arrays e.g. DataSetA (1 to 100), DataSetB (1 to 100) etc.
Is it possible to use a string variable (e.g. DataIndentifier) to identify and assign values to certain arrays.
For example, under certain conditions
DataIdentifier would be set to "DataSetA" and under other conditions DataIdentifier would be set to "DataSetB".
Having done this, I want to assign values such as DataIdentifier(1) = 3.4 or DataIdentifier (10) = 6.2 etc.
This obviously won't work because DataIdentifier is defined as a string not an array. How do I enable the string DataIdentifier to be used as an identifier for the array I want to assign values to? Therefore if DataIdentifier is set to "DataSetA" then effectively I want something like "DataIdentifier(1) = 3.4" to assign 3.4 to the array DataSetA(1).
I am doing a Regular Expression search on a string variable assigned to the HTML content of a Wikipedia search. However I am currently manually going to Wikipedia, searching for the term, saving the html page, opening the saved page with Notepad and then copying the content into a cell.
Can the above process be automated with VBA, how to assign the html content of a Wikipedia search to a string variable.
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.
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.
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.
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.
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
I have a workbook (excel07) that I use to import data onto our purpose built database. I have some macros to sort the data and send it to the correct columns however the one stage I still have to do manually is assign each row its own unique sorting Code (in a bespoke column "B2:B999").
These are objectives and the code should contain three seperate parts (part 1a and b do not need to be seperated);
1a. The characters "PO" should be the first in the code (for sorting purposes)
1b. The persons initials (first letter of the first name and last name) found in column J2:J999. [They are sorted by name so It would also be good to add a number after the initials seperated by a space incase of multiple objectives however I could defintly live with typing these in manually]
e.g. "Joe Blogs" second objective = JB02
2. The numerical part of the stategic objective they have selected found in column H2:H999. [incase you need this these range from 1.1-1.4, 2.1-2.5,3.1-3.6 and 4.1-4.5 all with a short text strings after which shouldnt be included in the code]
e.g. "1.1 We are Committed to being nice" = 1.1
3. The year they are applicable for which will all be "12/13" however I will need to change this next March.
Therefore the final code should appear as "POJB02 3.1 12/13"
There is a 16 Character limit on these codes but all of that information is needed for sorting, filtering and report generation, the spaces could be replaced by "_" if that makes things easier.
In order to deal with the multitude of screen resolutions that can be found at our work, I have got a module to check the current screen resolution on loading the workbook. I then have set up 3 different sized userforms, all containing the same coding, and based on the displayed screen resolution one of these is open (the userforms are named Customer1, Customer2, Customer3).
The displayed userform then has a button to show another userform (Customer_Details), and this form requires some referencing back to the previous form for some of it's information eg,
customer_details.textbox1.text = customer(1, 2 or 3).textbox1.text customerdetails.combobox1.list = customer(1, 2 or 3).combobox.list
way for me reference the specific "Customer" form that has been opened, without calling it by name?
