i have a small problem with a text box, its working fine, its on a seperate sheet to the one it passes the value to, its basically a quantity box, so the cell it passes the value to has a simple =I12*J12 formula in it, to multiply the quantity by the unit price, thus equalling the total priec of course. My problem comes if you change your mind, you put in the item, and the quantity, all works good, but if you want to delete the item you enter 0 or delete the value right? When i delete the value, the total cell brings back a #VALUE error, instead of 0, its almost like the text box is passing an invalid string to the quantity box, instead of nothing, if i manually delete the the value (even tho the cell is empty) from the linked cell, the #VALUE error goes away, any clues as to what the text box is passing that is causing hte error?
I am using a script that I found on this site (see below). The problem I am having is that I recieve an error on one of my sheets. The used range streatches from Column A to Column J and from row 1 to past 32767. I have found that if I delet the information below row 32767 that I do not get an error. Is there anything I can do out side of splitting the information up on different sheets?
Dim FirstRow As Integer, FirstCol As Integer, _ LastRow As Integer, LastCol As Integer FirstRow = Cells. Find(What:="*", _ SearchDirection:=xlNext, _ SearchOrder:=xlByRows).Row FirstCol = Cells.Find(What:="*", _ SearchDirection:=xlNext, _ SearchOrder:=xlByColumns).Column LastRow = Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByRows).Row LastCol = Cells.Find(What:="*", _ SearchDirection:=xlPrevious, _ SearchOrder:=xlByColumns).Column Set theRng = Range(Cells(FirstRow, FirstCol), _ Cells(LastRow, LastCol)) theRng.Select Selection.Copy
i am currently working on a macro that will make an average number of days. my problem is that when the macro runs it works fine and does what is is supposed to but becuse some fields do not have numbers in it returns the #value! back in the output box. I need some way of getting rid of this, or changing the boxes without numbers to 0 automatically.
I'm using Excel 2003 and I'm trying to make my LINEST function and the source cells for a scatter graph use the cell range specified in another cell in the document. I figured out that it was simple to do the following:
But I can't figure out how to do this in a larger function. I've tried a few things but none of them work. Here is an example where I want it to do a LINEST with y values in cells Ja - Jb and x values in cells Ia - Ib (where a and b are integers specified in cells I250 and J250 respectively):
I have a problem that when I try to convert text to number and format the number without 2 decimal places as seen on the link I have given below, Instead of 1607.947, I get 1607947. I have Excel 2010 loaded. The details are in below picture.
Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:
The included, small database is formatted as text. It is a text feed from an outside source. I simply want to format the cells into either numbers or general format but not text... seems simple, and it should be, but the only way I can get this done is to go to each cell and access the formula bar and re-enter the number by pressing Enter.
I used to get data from a database (CorVu & MIMS) in this format "0122458/001". Due to changes in those Databases I now get the data as 2 columns " 0122458" and "1" .What I need to do is somehow get this back to the old format including the leading zeros.
I am trying to find a formula that will count the number of unique entries there. I have tried the solutions posted on various websites to no avail (most recently:
I have a column C with different text in cells (item's title). Column D - relevant description for each of the items. 100+ rows.
Now, unfortunately, often a spreadsheet with items is updated with many new items. So I get a new spreadsheet with old and new items mixed. I need, somehow, to import descriptions of the old items (Column D of the old spreadsheet) to the new spreadsheet from old spreadsheet. So I want excel to look for old items in column A of the new spreadsheet and, once found, insert a description in the column B from old spreadsheet.
I can't seem to make user-defined format that puts a text in front of a number and/or a text.
Let's say I have A1: 13, A2: texttext A3: text7 and I want to format a lot of cells to "Ilike 13" / "Ilike texttext" / "Ilike text7"... ie add the same text in the front of the cell, no matter what the content is.
I did manage it seperately, with "texttext" @ for text and "texttext" # for numbers, but what's the general one?
I have a macro which will import data to the worksheet, then perform some formatting on the data, then assign the month & job description based on the lookup table. The problem is that when I import in the data, the data in column B&C will be store as text instead of number and the date in column E will store a 2 digit year instead on 4 digit year which cause error to my macro. I have try to preset the column format to number, i even try to change the column format to number when i run the format macro data. But the problem is still there.
What If we had to replace any number.. Lets say, if we had to seperate NUMBER TEXT NUMBER in different combinations.... B2 contains values like these then
TOM CRUISE 12 TOM 5879 CRUISE TOM CRUISE 123456789 123456789 TOM CRUISE 123 TOM CRUISE 456
[ = SUBSTITUTE(B2,"1234567890","") ]
I am at my wit's end pondering over it?
How to make the SUBSTITUTE function work for each individual digit?
I get a dump that is in the form xxxx gbps or xxxx mbps (gigabits and megabits). I'd like to either use a formula or VBA code to convert this to mbps in another column.
I have a standard block of text with numbers in it pulled from various calculations in a financial model. I have done this through a formula
e.g. ="You gross profit percentage is " & D9 & "% and your gross profit is $" & D10 & "." Problem is i'd like to format the numbers that pull through so they are easier to read. At the moment in the above example D10 results in $-600000000. I'd like it to look like $(600,000,000).
I have a couple of excelfiles in which someone made a flowchart with text boxes. I would like a search code that would help me find a text or number in those text boxes. The CTRL+F function only works on cells. Does something similar exist for text boxes? Or can this be made in a macro?
How do I pass values between procedures? I have two command buttons in the worksheet.
If I have a procedure A that asks user to input a value "x", when a command button is pressed. Then another command button is pressed that passes the value onto another procedure B, which iterates a msgbox as many times as the number x,
How do I pass that value x from one procedure to another?
I am building a tool that uses Pivot Tables (so I can't use track changes) and I have found cool code that will insert timestamp and username in the comment when a change occurs. But I need to modify the code to also take the old value and put it into the comment as well. Here is the code to register the change into the comment:
I'd like to know how to send a different value to a cell based on what is selected in the combobox. Like a vlookup, but from within the box. Something like :
I think this is a relatively easy issue, but I don't know how to do it. I call the Sub SortRange in another Sub SortDeliver. A range (ran5) is defined in Sub SortRange that I also need to use in Sub SortDeliver. How do I pass the range or get the second sub to recognize the range?
I've a userform (UserForm1) that on closing will open one of 5 other userforms. Which of the userforms that opens will be determined at runtime, depending on an option a user has previously selected. The name of the userform is held in a cell in a workbook and is opened as follows:
where the value in Sheets("Sheet1").Range("A1") is "UserForm3"
UserForm1 contains TextBox1. How can I pass the value in TextBox1 to a textbox in UserForm3 when the name of UserForm3 is held as a variable in a cell in a worksheet?
Option Base 1 Dim Covariance(1 To 5, 1 To 5) As Double Dim Mean(1 To 5) As Double Dim Portfolio(1 To 10, 1 To 2, 1 To 5) As Double Dim PortfolioValues(1 To 10, 1 To 2) As Double
And my main function:
Function Generate_Portfolio_Values()
Dim a As Integer, b As Integer, c As Integer Dim m As Integer
I have following code which identifies two worksheets, a source (wbSRC) and a destination (wbDEST). In the following code I am trying to get the values for wbsrc and wbdest to be written to the merged spreadhseet. But I am not sure how to go about this in VBA. The commented out approach does not work.
For Each rcell In wbList.Sheets(1). Range("A1"). CurrentRegion.Columns(1).Cells Set wbDest = Workbooks.Open(rcell.Value) Set wbSrc = Workbooks.Open(rcell(1, 2).Value) wbSrc.Sheets(1).Range(copyAddress).Copy _ Destination:=wbDest.Sheets(1).Range(destAddress) 'wbDest.Sheets(1).Range("q3").Formula = "Workbooks.Open(rcell.Value)" 'wbDest.Sheets(1).Range("q4").Formula = "Workbooks.Open(rcell(1, 2).Value)"
VB: Private Sub Workbook_Open() Call Meetdata End Sub
It calls this macro which is in a standard module:
VB: Public firstvariablename As String Public secondvariablename As String Sub Meetdata() regionname = InputBox("Enter the name of the Region.", "Region Name: North, South, East, West") meetdate = InputBox("Enter the date of the Meet.", "Date of Meet") End Sub
This set-up should make the variables available to all the other macros in the workbook. I have two other macros that need to use the values stored in these two variables that are entered into the two 'InputBox' statements. These two macros are in the same module and follow the 'Meetdata' sub. When I run the first macro, it recognizes the variables. When I run the second macro for some reason the variables are not recognized. To test the values returned by the variables, I placed the following code at the end of the first macro and again at the beginning of the second macro .
At the end of the first macro, the MsgBox returns the correct values stored in the variables which means that when the macro completes its run, the variables still hold their values. When I run the second macro with the MsgBox at the beginning, the Msgbox returns a blank. Somehow, the variables have been re-set to a null value. I can't figure out why the variables have been re-set to null. [URL]
I have created a few User Defined Types in a Main subroutine, but when I pass them into another sub the intelisense for the elements doesn't come up. I can type in the elements manually - and it all seems to work okay - but with a lot of types and elements I really want it to save me the effort.
