I have the code below in a macro which seemed to work fine but somehow it manages to convert cells in text format into "?/??" format when the macro is run. This bit relates to the part of the code highlighted in red below.
this is causing me major problems and need to fix it asap. I've attached a copy of the workbook if it helps. Unfortunately, to run the macro you will need to change the file directory to one that will suit.
I have a procedure that allows me to view and make changes to data in a table. I list the current values for the item in one column and use simple formula to copy that value to another column where if there are changes that need to be made, the formula is simply overwritten. The Macro is then selected using a command button and the formulas are all overwritten using copy/paste values to keep from writing out the formula to the data table. These values are then all written back to the data table, current values are overwritten with whatever is in the update column, new data or old data.
I have one cell out of 48 that has decided to march to the tune of a different drummer. The format changes from General to Text and the formula written from the macro is what shows up in the cell instead of the value of the formula. Never a big disciplinarian, I have to wonder if I have been too lenient on the cell and this defiance is the price I have to pay.
The sheet is protected only allowing entry into the cells available for update.
Here is the bit of code that affects this cell (starting from a format of General:
Code:
Sheets("Product Data").Cells(ItemRow, 3).Value = Sheets("Update").Cells(6, 8)
Sheets("Update").Cells(6, 8).FormulaR1C1 = "=(RC[-6])"
I just don't see anything that would change the format, and these are the only two lines that even reference cells(6,8).
I tried to set the format for the cell from within the Macro, but with the sheet being protected, it just dumps me out to my error message.
In order to export an excel table into another program I first need to save the excel file as .txt. The .txt file can then be imported by the other program.
First I however need to make excel understand that the value should be a text and not a value. I therefore format the number as text (0000150235) by adding "0000150235". After saving the file as .txt the format changes from "0000150235" to """0000150235""". I do however need the format in the .txt file to be "0000150235". Can anyone tell me how I can save "0000150235" as .txt and get the value "0000150235" in the .txt file.
Currently we do a lot of report crunching using Crystal Reports. We will then dump the data into an Excel sheet to run varying pivot tables or databases off of.
Often we will have a field that the formatting will not match up to anything in Excel. You can change the cell to Text, General etc. The main problem associated with this is running VLOOKUPs. The item we type in will not match up to the data imported from Crystal. We have to F2, the new data before it will match up which is a pain considering there can be anywhere from 30-1000 rows.
I'm trying to solve this problem in 2 ways. First can anyone tell me an easier way to do handle this?
Second, after trying many other things I thought about a macro that would automate the F2 process.
So I'm just a lowly intern who has been given the task of changing date formats in a bunch of spreadsheets. The string is as follows:
1015 Dixon Schwabl Est. for Creative Svcs 101207.pdf
I have to change the date at the end (mmddyy) so it reads as yyyymmdd. There is a long column of these strings, and i have to do it on over 300 spreadsheeys.
I would need a macro what would change this format (15 Jun 2009) in cell B2 to 2009.06.15.
View 5 Replies View RelatedWhen I hit the macro code you see below I get both columns D and E, starting from row 18, to get 'filled down' to the specified spot. Every second row has a conditional format (when a value is entered in column A) to change the row to the color grey, and every row between it has a conditional format (when any value is entered in column A as well) to have the row changed to the color red. The issue here is that the Macro code messes up the conditional formatting and uses the conditional formatting of those two cells, which are being dragged down, for those entire filled-down columns! This is what I am starting off with test1.xlsm and this is what I end up with using the macro code below (or doing it manually)
test2.xlsm
Is there a way for the Macro code to bypass this issue?
I am trying to copy part of a macro I use in one workbook which has a button which when clicked initiates the macro and changes the text displayed on the button from "Selection tree - On" to "Selection tree - Off". The code used in the original workbook (which functions ok) is:
View 2 Replies View RelatedI need in changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
I have a long list of dates that are fomatted as 3-12-13 that I want to format under custom date as d-mmm-yy : 12-Mar-13. I tried to do this but it does not change. I tried to change it to a number and that didn't work either.
View 2 Replies View RelatedIs there a way I can convert dates to text in a CSV file (opened via excel) using a macro?
Currently I have excel recognised dates in the first column of the format dd-mm-yyyy and a corresponding values in the second columns.
I know once I have the file open I can enter the following formula in an adjacent coloum "=text (A2, "MMM-YYYY") but I need to change the format in the existing date feild and not create a new coloum. Sure I could copy and paste the new formatted dates into the cell, but in terms of what I need to do, this is not practical as I several of these CSV files that I need to query and extract data from daily using a macro.
The macro is not currently working because the date format is wrong. I need it to appear as MMM - YYYY. When you view the date in excel it must appear like this (as it would appear in say microsoft word) and not with the underlying date format.
I have code that creates a textbox on a worksheet, copies the contents of a cell from another worksheet to that textbox. I want to be able to select that text and format its appearence ( center the text, bold and font style and size). This is what I have so far but I do not know how to select the text that was imported.
Sub textmove()
Dim bname As String
Sheets("cover").Shapes.AddTextbox(msoTextOrientationHorizontal, 96.75, 512.25, _
230.25, 120#).Name = "client"
bname = Sheets("data").Range("a3").Value
Sheets("cover").Shapes("client").TextFrame.Characters.Text = bname
With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
End Sub
What I am looking to do is create a Dos.bat file to be run in the middle of the night that opens Excel. From there I will need Excel to open a .txt file from a specific folder, format the file, then re-save as an .xls file.
Each of the .txt files in the specific folder is named like the following:
BCKLOG_062807
BCKLOG_062707
and a new file is being generated to the folder each night (with the new date in the filename).
Is there a way I can use the macro to loop through only the new files being placed in the folder, since the old files will already have their .xls counterpart saved ???
If this is not a clear enough description, please let me know and I will attempt to explain better.
I think there's a way to use Excel to automatically create a 5-day calendar. Maybe a macro?
The date format must be dd/mm/yyyy hh:mm and it must be in text format. If its June 9, 2012 at 6pm - the correct format is: 12/06/2012 18:00
I want to post 5 times a day: 09:05, 9:55, 10:45, 16:55 and 18:00.
Column A is where the date and time goes. Ideally, I'd enter in A1 the start date and then run a macro that would automatically populate the next 5 consecutive days with the specific times above (so 25 rows total in column A).
I have to save the file as a csv file, so the date and time format has to be in text or I'll get an upload error in Hootsuite.
I need to fixed width-text to column macro and found a reply in the forum.
However, when I apply the macro, the result of zeros in front of figures disappear since the format of value in splitted column doesn't predefined as text
e.g. sample text to split to column:
000122042009ABCDEFG00567
Required result:
0001|22042009|ABCDEFG|00567
when running below macro; result shows:
1|22042009|ABCDEFG|567
(Beginning zeros figures of the first and last column disappear)
Applied Macro:
I have data in these two formats 7/13/2014 9:24:14 AM and 07/12/2014 13:02. I want them in Am - PM style. I tried date , there is no format which suits my requirement. The first format in excel is general and other is custom under dd/mm/yyyy hh:mm . I have 23k rows, which I cannot afford to change manually.
View 3 Replies View Relatedi have a cell which has a time format. but currently its displaying 2.00907E+13. is it possible that i can convert it into the normal time format? for example B2 in my sample is 20090718170156 which actually means 2009/07/18 17hrs 01min(s) 56Sec(s) and after converting, how can i get the time difference between start time and end time?
View 3 Replies View RelatedI've got dates and times in the format of a text string. each string is always formatted in the exact same way to identify the data and time. As such:
Wed Aug 12 16:39:52 BST 2009
Mon Aug 10 14:39:03 BST 2009
Sat Aug 08 18:01:34 BST 2009
And I want to change them to a date formate that excel can understand, so i can search by month, or by date range. The main crux of the question is how to change that string into a excel understandable date though.
If you want to know, this is for my mobile phone bill. I have excel set up to give me a summery of how many texts I've sent, minuets I've used/have remaining. but i want to filter them by month, so I can see how my usage changes.
FYI I put up this question on another forum, however I'm asking it again here b/c I'm not really getting an informative answer there and this project is due soon.
[url]
I'm working on a file right now where dates are in the format "06/01/09" meaning 6 Jan 2009. Or more specifically thats what the data is supposed to mean, however when I change it to word form it appears as June 1 2009. This means dates such as "27/01/09" dont get translated.
How can I fix this? I've tried changing the date format, and I've changed the global settings, yet this isnt working.
In case its relevant, before I pasted this data into the current spreadsheet my global settings were set at month day year, which is what I prefer. But the rest of the office works on DMY so I'm switching to that.
my spreadsheet I have a column(7.65462E+11) it's a upc code.I can format the column to read as a number (value) but when I save it I does not save the number format.It's really interesting that there are some rows that read exactly the same and those rows keep the number formatting.Going nuts.Also when I try to save a date format(2013-mm-dd) It won't save,I've read that it's system default in U.S,is there a way around that ?
View 2 Replies View RelatedHow to translate dates which i already have in the US format, to the UK format.
i.e. mm/dd/yy becomes dd/mm/yy
Please take a look at the attached spreadsheet. in the ODDS column D, after download the some of the data has been changed from say 100-1 to a series of numbers how do i change them back i would also like to show the odds is they were 100-1 as just 100 and say if the odds were 13/2 then i want to show 7.5 basically i want th first number divided by the second.
View 12 Replies View RelatedI have a data set in excel which is the following format which I would like to convert into a text file with a different format. The excel sheet format is as follows:
V C 1 2 3
1 23 0.06 0.04 0.03
1 12 0.09 0.06 0.03
2 5 0.11 0.07 0.05
2 11 0.12 0.08 0.04
The text file should have the above data in the following format with brackets and comma:
(V,C,1)[value]
For example:
(1,23,1)[0.06]
(1,23,2)[0.04]
(1,23,3)[0.03]...........
I have a large chunk of data which I need to convert to a specific format which at first I thought was easy but im struggling to do it! I've tried concatenating and hlookup.
I want to convert this data:
Name
Age
Test 1
Test 2
[Code]....
I want to write down the number (149727273)
To become (1.49727273)
changing the format of sheet2 to exactly like the following image below.
*there are some codes in sheet2 which I think is the place to change the format.
Private Sub Worksheet_Activate()
Dim myDate As Date, mySize As Long, n As Long
With Sheets("Sheet1")
If Not IsDate(. Range("a2").Value) Or Int(Val(.Range("a4").Value)) < 1 Then Exit Sub
myDate = .Range("a2").Value: mySize = Int(.Range("a4").Value)
End With
I need to a macro to find any cells with the value 0.00e+00 (which is in scientific format - because thats the format i get from the software download) and change those cells to 00E010 and text format.
View 8 Replies View RelatedWhen I tried to import data in a CSV file, I ran into a couple of issues when trying to change the date format from MM/DD/YYY to other date format. I've already tried a couple things to no avail:
1. Use text to column functions and change the format from text to MMDDY
2. Use format cell function and change the date format
3. Use the text function (text(Cell,"MM-YY"))
Interestingly when I click on the cell and put my cursor and press backspace, the format will change. It seems like there's an extra space, but using the trim function does not work. I can't do this to thousands of data manually.
I have a list with several thousand dates listed like:
19931014
19900724
19910701
19911209
That is, yyyymmdd.
I would like to list it as yymm only. Is there a way to convert it in Excel? I have for a long time been copying and pasting in to Word then doing a search and replace of the first and last two digits, which results in the yymm format I am looking for. A better way would be nice.
I have many rows with dates I want to add a semicolon to. I do not want to type in each one, so I looking for a formula or something that will do this automatically.
I've tried formula, =A1&";" but this changes the format.
3/17/201341350;