Allow Contents To Be Replaced
Oct 18, 2007
I am writing some VB code to change a comma separated text to individual columns
Selection.TextToColumns Destination:=Range("O14"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=True, Space:=False, Other:=True, OtherChar:= _
"*", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1)), TrailingMinusNumbers:=True
When I run the macro, it asks me if I want to replace the contents of the destination cells. How do I get rid of this prompt? I have tried to clear the contents of the cells prior to running the macro, and have also coded:
Application.AlertBeforeOverwriting = False
but, it still prompts me.
View 2 Replies
ADVERTISEMENT
Apr 2, 2008
Using an excel file sent to me to fill out. File is 97-2003. When I type in text it shows up in the cell. However when I click to another cell the text in the previous cell turns to ########. I can double click it and the text reappears. I am unable to print the spreadsheet as I can only see the text when that cell is active...when I move to another cell to enter more text...can't print.
View 9 Replies
View Related
Dec 29, 2008
I have a "data" sheet that holds information on it and another sheet that has a printable form that uses formulas to extract and calculate the info based on what is entered into the user form. The issue I have having is in a certain number of cells, the formula is being replaced with the result. There are other cells that have the exact same formula and are fine. I can’t figure out why these certain cells are doing this.
There are no control sources overriding the data and I can’t see anything in the code that relates to these cells?
View 9 Replies
View Related
Oct 3, 2006
Replaced the period with a colon, 8.23 resulted in 8:23. I have since deleted the Add In. Now every time I enter a "." it is replaced by a ":". This happens even when I disable macros on the spreadsheet. I cannot see any VBA code.
View 2 Replies
View Related
Jun 4, 2007
How I might be able to streamline the following formula so that the #N/A is omitted (replaced with a blank cell) but there isn't quite as much of a time lag?
-->
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
-->
Here is the background information for what I am looking to achieve:
I have two spreadsheets I am working with. The first spreadsheet, the "What Computer has User Logged Onto Log" spreadhseet, dynamically imputs and displays data from the log file of a logon script: the date (column B), time (column C), computer name (column D), username (column E) and IP address (column F). The second spreadsheet, the "Quick Computer Jump Spreadsheet", has many pieces of information (including the computer name (column F) and computer description/room number (column P) -- which are the two important pieces of information there).
Yesterday I found a tip that allows for VLOOKUP via two different spreadsheets. Specifically to have the computer description/room number column (P) information from the "Quick Computer Jump Spreadsheet" filled in as column G in the "What Computer has User Logged Onto Log" spreadsheet. It was this formula here: =VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)
This was great because it meant not having to copy and paste the computer description and room number information into the spreadsheet I want to apply the VLOOKUP to and means I don't have to maintain and update the computer description and room number information in multiple locations, whenever that information changes. This formula worked beautifully, seems to be every bit as quick (with no visible lag) as when calling for the information from inside the same spreadsheet. In the case of dynamic data I could even use the Fill Formula all the way down to the bottom of the spreadsheet so that as other fields were filled, so was the computer description and room column. In short, it pretty much did everything I wanted it to
The next challenge I set for myself then was to see if I could get rid of the annoying #N/A when data wasn't found by the VLOOKUP function. Especially in those cases where I filled down below the last row in my spreadsheet in order to have the computer description/room number information automatically filled in as the other columns (A-F) are filled in.
I did some additional research and found this tip (here: http://www.mrexcel.com/archive2/47600/55248.htm). Using this information I was able to revise my original formula so that it now looks like this:
=IF(ISNA(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)),"",(VLOOKUP(D3,'C:Documents and SettingsMeDesktopQuick Computer Jump Spreadsheet[Quick Computer Jump.xls]PCs'!$F$12:$P$234,11,FALSE)))
This works but is unfortunately much slower than the first (I presume because of the double VLOOKUP required in order to compare and then execute?). Up to 30 to 45 second delays when the "What Computer has User Logged Onto Log" spreadsheet is refreshed.
I found the webpage info, here: http://www.mrexcel.com/td0110.html that had the following alternative ways to write the above formula that don't require the double lookup, thus making them much faster and more efficient to use. The problem here is the examples given are for absolute searches instead of non-absolute like my formula above and I have been unable to figure out how (or if) I can adapt any of these solutions to my own.
Does anybody know of any other way I might be able to write my formula above, to receive the same result, without having to do the double lookup, and thus decreasing the 30-45 second delay every time the spreadsheet goes out to update the computer description and room number information?
View 9 Replies
View Related
Jun 12, 2013
I have a master workbook (that I will call 'A') with a number of links to other workbooks ('B' to 'Z'). 'A' is designed to collate and display the data held in workbooks 'B' to 'Z'. The links all work fine until the 'B' to 'Z' workbooks are updated with new data, at which point, the links break with a "Error: source not found" error.
This is caused by the process by which we update the 'B' to 'Z' workbooks. We have to delete them, and replace them with new files, that have the same name.
Is there any VBA code or something that I could run that will update the links to pick up the new files?
View 2 Replies
View Related
Dec 8, 2009
I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
View 4 Replies
View Related
Jan 22, 2014
01. I want to Type "Al Raha Pharmacy" in a cell
02. When I type "Al Raha" and I press space, then the word automatically changed to "Al Rahma"
03. I tried this in other cells also but the result is same.
View 1 Replies
View Related
Dec 30, 2013
I have attached a workbook stating my problem.
file1.xlsx‎
View 10 Replies
View Related
Jan 14, 2013
I am trying to combine the contents of two cells depending on the contents of another, I have tried to use the If function but am coming up stuck!
I have provided a link to the example file below:
[URL]
View 4 Replies
View Related
Mar 12, 2014
I thought this was a pretty simple formula but I am having difficulty creating it. I am attaching a little test spreadsheet. Sheet 1 is where the data will be entered. The Reimbursed column has a drop down choice of yes or no. The next 2 columns are the cost of registration and the cost of accommodations. On sheet 2 is where I would like the formulas. So in cell A4 I would like a formula that says if B3 on sheet 1 is Yes populate this cell with the contents of Cell C3 only, B4 of sheet 2 would then be B3 if A3 on sheet 3 is Yes and so on with the Not reinbursed if sheet 1 the Reimbursed column is no.
View 3 Replies
View Related
Mar 25, 2008
I have a column in my .XLS whose contents I want to change depending on what the contents of the cells of a different column are. However, I only want two cells from the same row to be dependent on each other. So, for example, if $A$1 reads "1" then $B$1 should read "apple" and if $A$2 reads "1" then $B$2 should read "apple"; if $A$12 reads "3" then $B$12 should read "pavement" etc.
View 11 Replies
View Related
Nov 13, 2008
I have a list of Catalog which contains more than 5000 numbered items as shown below sample. Colum A is Material number and Colum B is the detailed description of material. The description in Colum B is written in one or more rows. I,e 90100100(A2) having 3 rows(B2:B4), the next material number (A5) having 4 rows(B5:B8)….. I need to apply a formula that Contents of all the rows of item A2 in one row in column C2 and contents of the rows of item A5 in one row in column C5…….till end of the list. Each row to be separated with a comma(,). Is there any formula or macro to fill for all the materials?...................
View 5 Replies
View Related
Aug 5, 2009
If “Dollar” is in cell B3 insert a row above it. If “Dollar” is not in cell B3 do nothing. Here is my current macro.
View 2 Replies
View Related
Jun 25, 2007
I have a formula that adds the contents of a row if a box is ticked. It then averages the result. This works fine, but now I only want it to add the contents of every other column before averging it. Can anyone adapt the formula below to allow me to do this?
=SUMIF(E73:P73,TRUE,E9:P9)/MAX(1,COUNTIF(E73:P73,TRUE))
View 9 Replies
View Related
Dec 18, 2009
Contents of Column C may well spread to over 18 characters, but column is formatted to be this wide - therefore if character length exceeds this, the data spills over into the next column. I don't want to use "Wrap Text" as I want to keep all rows the same height. I have toyed with this macro (which I'm sure came off this forum originally)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C5:C2200")) Is Nothing Then
ActiveWindow.Zoom = 90
Else
ActiveWindow.Zoom = 110
End If
End Sub
I quite like this as I can format Column C to "Shrink To Fit" and then read the potentially tiny font by clicking on the cell
What I'd really like though would be to keep the font the standard size for the sheet, but with a click be able to expand the cell to show the contents. I know that a simple cell click will show the contents in the formula bar, but it would be nice to actually work on the cell
On a related topic, again in the interests of neatness, is there any way to stop the "spilling over" of text into (in my case) Column D? This column is empty, and I can achieve my aim by putting a space into the topmost cell and then dragging down, but this seems a little messy!
View 9 Replies
View Related
Feb 5, 2010
i am trying to use clearcontents with the below code but i get an error,
With Sheets(1)
With .Range("J7")
.ClearContents Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
.Formula = "=H7-I7"
.AutoFill Range("J7:J" & .Range("A" & .Rows.Count).End(xlUp).Row)
End With
End With
View 9 Replies
View Related
Aug 30, 2006
I have a column with adresses.
Cell A1 contents Road 12
Cell A2 contents Street 555
Now i want to split the content.
In cell A1 Road and in cell B1 12
In cell A2 Street and in cell B2 555.
Is there a macro that can do that?
View 9 Replies
View Related
Sep 10, 2013
I want the function of clear contents to work based on the value of B4. If I run the code with clear content part excel crushes.
VB:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("B4") = "Basic" Then ''*** B4 is adropdown ***''
ActiveSheet.Unprotect
'Range("B10,F10,H10").ClearContents ''*** if I run this part the excel crushes ***'''
Range("B10,F10,H10").Locked = True
[Code] .....
View 4 Replies
View Related
May 2, 2014
If Cell A1 Contained 1,154,546,487,47,36,48,47,668 and so on. Using a Formula or Macro how can I get it to take each number(s) before each comma and put it in the next column on the same row?
So using the above example numbers you would end up with 1 in column A1 and 154 in column B2 and so on and so forth.
View 8 Replies
View Related
Dec 26, 2013
I have a cell that contains values such as "AAPL" or "0001.HK" (without the quotes). I would like to extract the content to the left of "." (if any). i.e. I want the cell to return "aapl" or "0001". What formula should I use?
View 9 Replies
View Related
Dec 24, 2008
I want to have a cell that looks at another cell based on a number that is in another cell. So imagine if you will: A1 contains the number 25. A2 contains the formula I can't work out. I want it to look at the row that is the number contained in cell A1, so in this case it would look at row 25.
Now I want to narrow it down further to a cell in that row but this will be a constant so lets say column F. So if A1=25 then A2 would display what ever is in F25. If cell A1 were 16 it would display the contents of F16 etc. The best I got was something like =IF(A1=>1,(F(A1))) but obviously that is wrong as it doesn't work.
View 2 Replies
View Related
Aug 28, 2009
I'm trying to work out if there is a way to rearrange the contents of a cell. Basically, I have names in each cell which have surname then first name and I want to have them reversed.
I know it would be easy if they were in seperate cells but unfortunately that's not the case. Is there a way to do it? If it's any help surnames are in upper case and first name in proper case.
View 11 Replies
View Related
Feb 10, 2010
I am looking for a way to remove text from a cell if it is 2 characters or less but do nothing if the text is longer than that. Is there a way to do this with either a function or an advanced find?
For more detail, I have a list of middle initials and last names. The cells are either a middle initial by itself like "D." or they are a last name like Jones. I want to go through and delete the cells that have the middle initial and leave the ones with the last name. So if there is a function that will evaluate the cell, and if it is 2 characters or less, then delete the contents. If greater than 3, leave it alone.
View 3 Replies
View Related
Feb 9, 2013
About half of the time when I move the contents of a cell I get the message "The Clipboard cannot be emptied. Another program might be using the Clipboard"
View 14 Replies
View Related
Mar 12, 2014
How can I lock the contents of an array so that they won't disappear when using auto-filter. "Freeze Panel" doesn't seem to do the job properly.
View 9 Replies
View Related
Jul 3, 2014
I have a range of cell content to be clear (Value only) but no formula in those cell.
The range is populated in cell H106.
Example cell
if cell H106 = D5:E102 then clear the value value only in cell range D5:E102
if cell C106 = G5:H102 then clear the value value only in cell range G5:H102.
View 8 Replies
View Related
Feb 13, 2014
Effectively I have a cell that contains the following data :-
63303-9600
63303-5280
63303-9700
What I need to do is loop through this cell and take the first 10 characters and ouput to a row, then take the next 10 characters and output to the next row and so on until it has been through all the cell content.
View 1 Replies
View Related
Mar 6, 2014
I think I've had enough of using excel generated macro's as an excuse for decent code. I need to turn this:
Small procedure, function, what have you that will continue this pattern until I hit some kind of indicator to tell it to stop. This just clears the contents from 7 rows, hops down 9 rows and does it again.
View 14 Replies
View Related
Oct 6, 2008
Have tried a few things but can't get it to work properly. I have attached the file I am working with because it is much easier to understand if you just look at it!
Basically, I have a single sheet with three columns:
Name, Post Code, Tenant
There are varying instances of 'Name' and each one has a different 'Tenant'.
What I need to do is merge the contents of the Tenants cells so that for each different 'Name' there is only 1 instance and then for Tenants all the different cells are merged i.e. "Tenant 1, Tenant2, Tenant3...etc"
I hope this makes sense, as I mentioned above it will be a lot clearer if you have a quick look at the attached spreadsheet.
View 14 Replies
View Related
