Column B has list of Balance Sheet items for business and Column D has associated values. I need to perform the following:
Look for following text and if it finds it then change the font size to 16, bold it,center align and make the associated cell in column D as mentioned below.
ASSETS ....change the cell color in column D to white (Invisible).
Cash and Bank Accounts
TOTAL Cash and Bank Accounts
Other Assets ....change the cell color in column D to white (Invisible).
TOTAL Other Assets
TOTAL ASSETS
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.
I have an excel sheet full of dates in text format and want to convert them into regular format. For instance, one of the dates listed is in text as "60801". I'd like it to show in regular date format of mm/dd/yyyy, so that 60801 becomes 8/1/2006.
In D2 I have: =B2 In D3 I have manually input: (space)3-08
As you can see the CONCATENATE puts the "39569" date in A2 But the second line puts the text date as I prefer. What I would like to do is put in a formula or macro in D2 and down that will change the "Mar-08" to "3-08" so it CONCATENATEs correctly to column A. Simply: I'm trying to avoid manually inputing the text version "3-08" (or whatever M-Y) into D2 down a hundred or so rows!
Need to create a set of aligned text amts from various cells...Tried Format but unable to get right combination...I've looked at many threads and most seem related to getting amt from text instead of reverse.
Cell may contain nothing or a monetary amt, negative or positive. Output needs to be in format of "$9,999.99-" or similar...with leading zeros suppressed but a min of "$ 0.00" showing, so that above/below amts with be decimal point aligned in a fixed font situation.
I've almost gotten my routine finsihed but this is last remaining obstacle.
I'll get straight to the point: How can I change this text format from 474556788 to 0-47455678-8 (dash added after 0 and before the last number). I need to apply this to around 5000 rows (can't do that manualy). I want it to be int his format:
474556788 to 047-455678-8
Tks Auto Merged Post;474556788 to 047-455678-8 (This is the wanted format)
On the attached example i have a list of fractions (in the format ?/???). However, i have a problem when the fraction is 6/4 or 4/6 as excel rounds the nominator and de-nominator down to the lowest value so these fractions become 3/2 and 2/3. For these two fractions only i don't want this rounding down to happen.
I am open to any suggestions, but i was thinking of some vba where on pressing a button it would run a macro that went through the cells in the column and if the cell value is 1.5 (3/2) then format the cell as text and enter the value"6/4". For the 2/3 fraction, i think you may need to say if the cell is greater than 0.66 and less than 0.67 because this would possibly be harder to exact as the number of decimal places involved.
How to format different numbers to the same format (as text). Mainly I need code that would format numbers like 25, 25.36 or 254.60 to numbers looking like this 000002500, 000002536, 000025460. They must be of nine digit length with the last two digits as decimals.
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 few tasks which I have been doing manually because I do not know if there is a way to accomplish these tasks automatically via some excel formulas.
My first task requires me to take an address, for example: 1234 CAMBIE STREET, and format it such that it is properly capitalized (only capital letters in the front of words) like this: 1234 Cambie Street
My second task involves taking an address, for example: 1234 1st Avenue W, and rearranging it such that the direction is in front of the street name like so: 1234 W 1st Avenue. What makes this task potentially even more complicated is that not all the addresses I am working with require the rearrangement, so I can't simply have a formula that puts the last group of characters in front of the first group of characters because that may screw up addresses that don't require this formatting.
My third task demands that 2 columns of names are combined into one column, while also simultaneously placing a "&" between the names. For example: Jonathan Parkinson | Sarah Parkinson turns into Jonathan Parkinson & Sarah Parkinson. Now I do understand how to use a simple combining formula (=a1&" & "&b1) but it isn't that simple. The columns are not all filled with names. Some pairs may have no names, whereas others may have only one name. Because of this, the formula I used as an example will result in many instances where all I see is a "&", and other instances where I see the first name, followed by an unnecessary "&".
So far, for the majority of these problems, I've been using a combination of manual data input, and the replace function, but I really do hope there is a faster and better way to go about these tasks.
I have problem to change text code into numerical code using macro. i have data contain text code and i would like to convert it into numeric . each text code has dedicated numerical code for example I have 4 fluids with text code text code:
FW = Fresh water SW = Saline eater CW = connate water MW = Meteoric water
numeric code as follow
FW=1 SW=2 CW=3 MW=4
I would like to convert the text code into numeric code, it is easy if using excell , but it is routine job for me , i need to create macro to be more simple .
I m creating a sheet in excel where I need to do some conditional formatting but need more than 3 rules so i'm guessing i'll need to use some vb code.
Basically I have a range of cells say from c12 to ag15 These cells all have formulas in linking to other cells in other sheets.
Tha values that will be in them are as follows: s, h, hd,ooo,z and maybe 1 or 2 more will be added later. I need the cells background and text colour both to change to a colour depending on the value in the cell.
eg. cell value = s then background and text colour both to be yellow.
I'm looking for a code that automatically changes the format of any pasted in data to Match destination formatting. Not getting the results I want from protecting the sheet.
I keep getting the "Else without If" Error and not sure how this code works.
Code: For Each oBookMark In oDoc.Bookmarks Set objX = wsData.Rows(1).Find(oBookMark.Name, LookIn:=xlValues, lookat:=xlWhole) If Not objX Is Nothing Then Select Case oBookMark Case "NetToServicer", "PurchasePrice", "MaxCommission", "JrLien", _ "HOALiens", "Repairs", "Termite", "RelocationAmount", "CashContribution", _
This code checks file's date and depending on the date of files creation it has to color cell and show date (if the file is older than today) or time (if the file has been created today).
Generally this code works fine except one thing, line "Range("G14").Offset(j, i).NumberFormat = "mm/dd"" and Range("G14").Offset(j, i).NumberFormat = "hh:mm"" work every second run of the code.
Meaning, I have to run this code two times on exactly the same set of files. When files change (for example new have just been saved), first run changes color of the cell but doesn't change the format. Second attempt works fine.
I tried moving these lines at the end of all formatting lines and even repeat lines with NumberFormat again but no change.
I currently use some Excel VBA to copy graphs from different sheets in an Excel workbook to a new Powerpoint presentation (one graph per slide). The graphs in Excel are simple line graphs with weekly dates UK style (dd/mm/yyyy) on the x-axis, and values on the y-axis. All works well when these are copied and pasted into PowerPoint as pictures in their own slides, except that the dates become Americanised (mm/dd/yyyy). I need to write in the VBA code to preserve the original date formatting? I am unable to attach the Excel and PowerPoint files - I hope the explanation above is sufficient.
Can Attached code be changed to alter font colour,type. rather than just fill colour.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim icolor As Integer If Not Intersect(Target, Range("e:Az")) Is Nothing Then Select Case Target Case "Ph" icolor = 6 Case "Li" icolor = 12 Case "O" icolor = 7 Case "An" icolor = 53
Was hoping to get some help with a mini project of mine.. Currently I am looking to create a progress/commission report (pull data from an access dbase) and populating an excel sheet. I can manage to pull in the data fine its just gettting into a format that is the complicated thing. I was hoping that someone might have a few moment to pick thru my code (see attached) and maybe suggest an alternative/more efficent way to do the refreshing.
I am trying to insert data from an excel sheet to a sql database. Everything works okay but the Time value. In the Excel sheet it is '12:09 PM' and when it is read with 'cl. Offset(0, 6).Value' it turns it into '0.50625'. Is there a way to format it so it can be inserted as a datetime?
Dim cl As Range For Each cl In Range("A1:A12") stSql = "INSERT INTO GasData (Delivery,OpenPrice,High,Low,Last,Change,Time) VALUES ('" & _ cl.Value & "','" & _ cl.Offset(0, 1).Value & "','" & _ cl.Offset(0, 2).Value & "','" & _ cl.Offset(0, 3).Value & "','" & _ cl.Offset(0, 4).Value & "','" & _ cl.Offset(0, 5).Value & "','" & _ cl.Offset(0, 6).Value & "')" cn.Execute stSql Next
I have a macro which is designed to collect information and then assign each piece of information to a defined cell. I have Dim statements for each piece of information i.e.
Dim Info1 = Surname Dim Info2 = Initials And so on. Dim Info11 = Date of entry
This is working perfectly but the date when entered is reversing to american format. 08/05/2006 becomes 05/08/2006. Type into the cell manually and it works perfectly let the macro put the date in and it changes format. Can anyone supply a line of code that would format the date to dd/mm/yyyy? Then I could insert it after the part of my code which basically says go to this cell and put in the date.
I'm try to get a macro to do a 5 Colour Conditional format. I've searched the previous questions as recommended, and found code that I think will do the job. I've tried to modify it to my own specs, but I can't get it to do want I want. Basically I need it to colour code the cells based on the ball number within them. As I can't get the code to work, I don't know if the colour values are correct? Also given that cells are white by default, balls 1 - 9 don't need to be part of the code do they? I've attached a small example of what I've managed so far, with the macro code enbedded. Also what's the correct way of cpoying & pasting macro code from one spreadshhet to another? The simple copy and paste seems to stop the code working correctly. Any help would be greatfully received.
In the sample i have list of codes,also i have picture according this codes: Can i somehow conected pictures and codes? My pictures are in jpg form,can i insert them into cell,where picture should has form as cell has.
When the code from the Data sheet in cell A2 sheet 1,than show the picture conected with this code in Data sheet.
