I have a formula that takes various data from a worksheet and places it all into a single row (N2:AY2) using the “=” formula. I then copy this and paste values, then I copy it again to it can be pasted into another application. There is also some various hiding/unhiding that goes on.
Question is, when I paste values it will truncate some numbers. For example if I have $70, it will change it to $7. 11/10 turns into 11/1.
When I use that function on a UserForm it executes the code immediately, not waiting for the value to be entered. Thus the code executed that relies on the new values returns an incorrect value.
Example
A1 | 150 A2 | 100 <-- this is the value being changed to 300 with the Textbox A3 | 250 = Sum(A1:A2)
I have a little bit of problem with lookup function. When i paste values from another worksheet (paste special, values) in a cell which is lookup value i get #N/A. These values are numbers. When i put '7 for example i get the values i want from lookup table. I have a lot of these cells and its tedious job to put ' in front of every value. Is there a quicker solution?
I'm trying to truncate (not round) a percentage, for example if 6.25% to 6.2%. I'm having issues figuring this out because the percentage already in the cell is a result of a formula (i.e. SUM=A1/B1.) Does anybody know a way to acheive this without having to sacrifice the formula already in the cell?
I have many different numbers that are sperated by dashes
example Q1456480-2-BLK---L Q1456480-2-BLK---M Q1456480-2-BLK---XL Q1456470-7-MRNR---L Q1456470-7-MRNR---M Q1456470-7-MRNR---S Q1456470-7-MRNR---XL
I want to take off the end the ---L, ---M, etc but don't know if there is something that will do that like a right formula but not.
And then I want to isolate the color which is 3 or 4 characters before the --- Last I want the style number which varies in length and goes to the - before the color.
This formula finds the last space from the right and returns the values less the last space and whatever is to the right- =TRIM(IF(LEFT(Y25,2)="~C",REPLACE(LEFT(Y25,FIND("`",SUBSTITUTE(Y25," ","`",LEN(Y25)-LEN(SUBSTITUTE(Y25," ",""))))),1,FIND(" ",Y25),""),RIGHT(Y25,LEN(Y25)-FIND(" ",Y25))))
Results of a item of a whole size: LAKAI KOSTON SLCT INDY SHOE BK
However- I forgot I had added a criteria to convert records that have a fractional value from decimal to a fractional value and an extra space shows up. I need to alter the above formula to handle this additional condition, returning the value w/ out the second space and the size 10 shown below.
Results of an item w/ a fractional size, ie. 10 1/2:
LAKAI KOSTON SLCT INDY SHOE BK 10
Linked to this post: new post started to help avoid confusion: but wanted to show prior material... http://www.mrexcel.com/board2/viewto...766e4507d1a402
I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.
I have 12000 rows in cell a1 with more than 30 characters in them. I would like a formula to go up to 30 characters from the left, then go back to the first empty space, put that info in B2, the rest I would like to put into c2.
A1 This is a test to show what who to do this. So I need B2 This is a test to show what C2 who to do this.
I would like to limit a text cell to 40 characters. If greater than 40 characters are entered, the rest is truncated. Can this be done without VBA?
I have limited it by going Data - Validation
This has done two things. 1. Gives an error box with Error Alert Turned on. 2. Allows greater than 40 characters to be entered if Error alert is turned off.
Attached is my code, pay attention to the bold part. I want the sourceSheet to be copied as a sheet and pasted in the targetSheet (the Sheet2 of "NewBook") but I want it pasted asvalues. Here is the specific part which needs to be looked at...and below is the full code.
VB: Set sourceBook = Application.Workbooks.Open(sourceFilename) Set sourceSheet = sourceBook.Sheets("Current") Set targetSheet = NewBook.Sheets("Sheet2")
Paste can this be set to default to Paste Special Values only ?
I have a sheet with a number of lists validated drop down boxes. The sheet is networked and works fine.
Problem other users as they are entering information into the required cells they are copying and pasting. Then the inevitable occurs they paste data into the wrong cells the validation from the original cell is pasted as well. Is there a way I can set paste special values only to be the default for the full sheet.
I'm trying to paste some numbers into cells that look like this:
2-3 1-4 4-5 7-8
When I try to paste those into my excel sheet it automatically changes it to a Date. Even if I format the cells to text first the paste copies over my settings and changes it to dates. I tried all the paste special options that come up and none work.
I have a bunch of numbers which were generated using a function. When i try to copy and paste the number to another spreadsheet i end up pasting the underlying function-formula instead......
Is there a way to copy and paste what is seen in the cell, not what is underlying?
In a word file, I have a list of ID numbers. I want to paste them into a excel file, but the problem is that the last 3 digits of every ID number are converted to "000" no matter what they are. The ID number has 18 digits.
We have a template sheet we use which has a sheet formatted as 'text' - it msu remain this way for another purpose.
We wish to copy information from other sheets/places and paste into the template sheet - but have it as text (with green triangle on it)
I have tried copy and pasting (the sheet is already formatted as text. I have tried copy and paste special as values but to no avail.
(I have attached a small example which shows the template, data to past and end result of what I require - the redtab sheets are attempts to paste!
I know there is a really simple solution but I cannot for the life of me see it!!!!!!
Please help! Auto Merged Post;I kept searching and have found a way to do it on 1 column... (Data/Text to columns....text) - but how do you do it over a sheet that contains say 120 columns (i.e.: en masse)..???
I am having problems with the following formular in that it does not return what I am expecting.
I have 2 columns of data that hold Y/N values and I would like either "Yes" or "No" to be displayed in Col C for the 4 possible combinations that the interaction of Y and N in columns A&B give.
The data is Col A:A = Y or N Col B:B =Y or N
The four possible combinations and outcomes are: Col A = Y & Col B = Y outcome in Col C = "Yes" Col A = N & Col B = Y outcome in Col C = "Yes"
Col A = N & Col B = N outcome in Col C = "No" Col A = Y & Col B = N outcome in Col C = "No"
The formular I have tried is: =IF(OR(AND(A1="Y",B1="Y"),A1="N",B1="Y"),"Yes","No")
However while three of the outcomes for the combinations are correct (N/Y, Y/N, Y/Y), the output for the N/N combination is incorrect and returns "Yes" instead of "No".
Just wondering if anyone has any ideas as to what is wrong with this?
I've created a spreadsheet to 'translate' number/letter combinations (sub-levels in the UK education system) into a numerical value. I looked up how to do this on google and, through a bit of trial and error, worked out that the lookuptable has to be in alphabetical order, etc.
My problem is when I enter in any of the 'P' values (see attached), it returns 0.5.
I have the following code, taken from an example off the web. But I would like to change the save path to just C/: rather than copy the save path of the original file...I would also like to not close the active workbook that I am working in....how can i achieve this?
Sub TwoSheetsAndYourOut() Dim NewName As String Dim nm As Name Dim ws As Worksheet
If MsgBox("Copy specific sheets to a new workbook" & vbCr & _ "New sheets will be pasted as values, named ranges removed" _ , vbYesNo, "NewCopy") = vbNo Then Exit Sub
With Application .ScreenUpdating = False
On Error GoTo ErrCatcher Sheets(Array("Copy Me", "Copy Me2")).Copy On Error GoTo 0 For Each ws In ActiveWorkbook.Worksheets ws.Cells.Copy ws.[A1].PasteSpecial Paste:=xlValues....................
I have two pieces of code in module 1 and module 2 respectively:
This code is meant to find the first row number that has a string value of "" in column A:
HTML Code: Sub Macro1() Dim cell As Range Dim r As Double Dim p As String For Each cell In Worksheets("stock in").Range("stockcode") p = cell.Value r = cell.Row If p = "" Then Exit For Else Next cell End Sub
BTW column A will contain formulas that evaluate to "" so I assume cell.value will = "" even if it contains a formula?
In the second module a sort is executed on the range based on the number of rows:
HTML Code: Sub Macro2() ' ' Macro2 Macro ' Call Macro1
My code is falling down because I think my variables arent scoped right or my code is in the wrong place. Also macro1 will eventually be executed from a button on a different sheet (I dont know if this will matter).
The whole point of this code is to get around the fact that if you perform A - Z sorts on cells that contain formulas those that evaluate to "" will be at the top leaving lots of blank rows before actual visible data.
I have a line of code that compares cell values with a variable.
Code: dim rmycell as range If rmycell.value = MyTarget then
This works fine when rmycell.value is empty or has a value. However, sometimes rmycell has a #VALUE! Error. When debugging the code I can see this error showing rymcell with a value of "Error 2015". The MyTarget variable is "abcdefg", so the If statement should resolve as
Code: If rmycell.value = MyTarget then becomes Code: If Error 2015 = "Abcdefg" then
Which should be a negative result. Except it isn't. The code that is conditional on a positive result runs whenever there is a #VALUE error, just as if the 2 values were equal.
I'm trying to create a new function in Excel and for some reason the syntax is incorrect and it won't let me use it in the sheet. I do not know what I'm doing wrong so any help would be appreciated. This function will allow me to interpolate the term structure of interest rates between dates (I think) My code is
Function INTSPOT(spots, year) 'Interpolates spot rates to year Dim i As Integer, spotnum As Integer spotnum = spots.Rows.Count If Application.WorksheetFunction.Count(spots) = 1 Then 'Single rate given INTSPOT = spots Else 'Term structure given If year = spots(spotnum, 1) Then INTSPOT = spots(spotnum, 2) Else Do i = i + 1 Loop Until spots(i, 1) > year INTSPOT = spots(i - 1, 2) + (spots(i, 2) - spots(i - 1, 2)) * _ (year - spots(i - 1, 1)) / _ (spots(i, 1) - spots(i - 1, 1)) End If End If
In column H I have a list of numbers seperated by a space, the number of lines can change. In column L I have a list of numbers which can change either expand or retract.
I would like to check each cell in column H and if any numbers are not listed in column L then it/they should be shown in column G.
Example1 H2 shows 6 11, therefore cell G2 should show 11.
Example 2 H6 shows 5 6 9 11 therefore G6 should show 9 11
Sheet1 HIJKL1ContainerID26 11135 8 11245 7 11355 7565 6 9 11675 6 9 Excel tables to the web >> Excel Jeanie HTML 4
CTRL/END key combination should goto and select the last cell in a Worksheet. So for a Worksheet using Rows 1-4135 and Columns A-Z it should indicate Z4135 as the last cell My current worksheet uses these Rows and Columns, but CTRL/END indicates last cell as being AB4506. (Inflated by 371 Rows and 2 Columns)
Deleting the unwanted Rows or Columns, or "Clearing contents" of them does not affect the result given by CTRL/END which still shows AB4506.
How can I adjust/correct the last cell indication?
Usual work-around is to select the wanted portion of the worksheet and copy it to a new blank worksheet, but is there an easier way?
I have a userform on which there is a frame containing 8 option boxes. After a query, the results are displayed in the form. If I have a value of 1 in a cell, optionbox1 is checked; a value of 2 checks optionbox2, etc. It works great EXCEPT when first initialized. At that point, it checks the last optionbox, even if the number is 1. I have built a next and previous feature to scan the data, and when I return to the first entry, the correct box is checked. I tried coding blanks into the fields prior to populating them, but I still get the same results. Is there some explanation available so that I may remedy this? I'd really like the first piece of data to be correct.
