I have a cell (A1) that, depending on a choice made elsewhere in the workbook, will return A2, A3, A4, or A5, which are all formatted differently with superscripts. How do I get that formatting to copy over to A1 without having to manually copy and paste format from the appropriate cell? I have to do this for a range of cells (A1:A15) instead of simply one.
View 4 RepliesI have a scheduling spreadsheet that I designed for excel. It uses numbers as text and vlookup values instead of calculating actual time (its easier and saves space). I want it to look aesthetically pleasing:
630-230 (that is six-thirty until two-thirty)
7-315 (that is 7 o'clock until three-fifteen)
should look like this:
630- 230
7 - 315
How should the code be written so that if I write a string of text "xxx-xxx or x-xxx" the right parts get superscripted automatically.
notice that the first example has 3 digits before the hyphen and the second example has 1 digit before the hyphen. I need the vba code to work no matter which example is used.
When using format cells > number >custom I want to custom oC with the degree symbol in superscript. how to get superscript into the custom box? Also where is the superscript icon for the quick access toolbar in Excel?
View 3 Replies View RelatedI have an amount of text in my spreadsheet, which contains superscript numbers and I want to get rid of these numbers.
Is it possible to do this with a UDF or a macro?
I include a simple example spreadsheet with the situation as it is, and how I would have it.
I want to superscript the text with red color. But now only a active cell is change to superscript with red color after running the macro.
View 2 Replies View RelatedIs there anyway to superscript font in userform such cm2 (you know what I mean) instead of cm^2.
View 8 Replies View RelatedText to column function. My database includes dates and names of medications separated with superscript numbers. The numbers are not fixed. So I need a separation between any superscript figures.
Example:
³4002/60/61³51³4002/50/11³gm³bat gm52 nanizon³מרוסק³ ³³006³4002/50/60³gm³stelbaT gm02 livomiS³³ ³³01³
The data is in reverse order but this was solved already.
Is there a way to superscript or subscript only part of a cell's text? The forum below provides VBA to superscript an entire cell.
How about just part of a cell?
e.g. Say I type "The volume of the cylinder is 42 m3", the press the arrow key once (to have the cursor before 3, hold down shift, go forward to select 3. NOW, I want to run a macro or script to superscript the selected part of the text, in this case the 3.
http://www.mrexcel.com/board/viewtop...ic=856&forum=2
How about using an inputbox to choose part of the cell?
Or using an inputbox to ask you to choose the cell,
THEN counts number of characters,
THEN asks you where to start and stop the supercript or subscript,
THEN asked if you want to superscript or subscript
THEN asks if you want to superscript/subscript (depending on what was choosen) "selectect text"(have excel store the text part selected for formatting),
THEN carries it out?
Is this possible? I don’t know enough VBA to know.
Here's some code to get people started..
Range("A1").Select
ActiveCell.FormulaR1C1 = "m3" .........
I'm working with a data logging system to collect data on a circuit. The logging software allows you to export the data collected to Excel in real time using DDE, and having talked to the logger company I managed to obtain a macro to get the data into excel (I'll put it at the end of the post). This works fine, and I can now press a button and Excel updates itself with the new data every second. What I want to do is quite complicated and I have no idea where to start:
1. The data obtained via the macro only displays in static cells, that is when the new data comes in it overwrites the old one. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column
2. Having a time elapsed measure would also be very useful, so that it records the time after starting the other macro in a column next to the ones collecting the data as described in 1. In this case, each new row would be 1 second further on. The actual time is not massively important, just the 'relative' time since the data collection started.
3. Once the data is copied into the columns to be stored (1.) I then want to calculate two sets of moving averages and place those in columns next to the data. My current plan is one average of 30 samples and another of 5.
4. Once I have the moving averages I can then get Excel to plot some graphs to show the progression of the two values
5. Since my project is revolving around detecting a short circuit in a coil of wire, it is looking for drops in resistance. I want Excel to be able to monitor the two calculated moving averages and if the difference between them exceeds a set threshold for more than 5 samples then some alarm is triggered (for now just a cell changing colour to red or a pop-up box or something).
I would like to give credit and thanks to StephenR for producing the current Macros in use on this spreadsheet.
This was to sort out Raw Data that I get from a database, and with lots of help sort it and format so for a history of this you can look there.
Now I need to enter in some formulas, data from another spreadsheet and cell formatting…
Unsure if all this is possible but am hoping some of it is…
First Thing
The column Run Time has the data in them already this is the time in minutes am looking for a way to put a formula in, that will make them into hours so the formula will read like this “=180/60” this will give the number 3 as the whole number.
Second Thing
Using the whole number that the formula made in the Run Time column, am looking to fill cells with colour, too see what I mean here its best to take a look at my mock up spreadsheet.
Third Thing
Two rows below all the data that gets sorted out I would like to put in two totals, one for Run Time and another for Available Hrs “=SUM (Range)”
Forth Thing
I have data in another spreadsheet that is the Resrce Name, and Available hrs am looking to take that data from this spreadsheet that will be closed opened if needed, this data will assign workers to Work Centres.
I would like this to be a separate macro to run though all the sheets if possible.
I have only recently started playing around with macros, and am slowly getting into them. I do however still struggle a bit to ‘read’ them. Having played with formulas for years I can generally translate a string of formula text into English, like ‘if this cell value is greater than that cell value, then do this, if it is not then if it is equal to that cell value, do that, if not return 0’. Babbling like a child basically.
With visual basic I have managed to record some handy macros and then tweak them a little manually but I am still struggling to follow it going through it step by step reading it like a formula. So I hope you won’t mind me asking some very stupid questions. I mean well; I’m just a bit slow.
At present I am trying to do two different things on two different sheets, and I was wondering if perhaps one of you could nudge me in the right direction.
1:
I am trying to insert a blank row above every row that has a certain word in column B.
So basically ‘find value “Example” in column B, and when you find it, insert an entirely blank row directly above it’.
2:
Is a bit more challenging. I want to change the colour of a cell if the value in the cell corresponds with the value of another cell in the same column.
For example, I have a long list of surnames in column A. When I add ‘McNeil’ at the bottom, I would like to be able to run a macro that checks if the name McNeil appears anywhere else in column A, and if it does, that it changes the colour of the cell.
Preferably both of the cells that say McNeil, but one would do very nicely indeed.
If that is possible, I wonder if it is possible to do the same with the first name in column B, but only if there was a match for the surname in column A on the same row. So, if McNeil does not appear in column A, don’t bother, but if it does, does the corresponding first name appear in column B?
If both of that is possible, the next step would obviously be if McNeil appears in column A (say twice, once in A123 and once in A678), do cells B123 and B678 match as well?
I am totally new to Macros. I need a Macro which should format a column based on the value of another column.
Consider I have 10 rows. I have to format column D, based on the value of Column E. If the value of Column E is > 1000, then the background color of Column D should be changed as green. The most important requirement is Column E should be invisible, Changing the font color of Column E as White does not seem ok cuz when we select the sheet entirely using Ctrl+A, the white values are very much visible. Can this be achieved using a macro?
I have an Excel 2003 program that contains macros. One of the macros hides certain command bars and disables the worksheet menu bar. On close the opposite is true. The problem is, if a user uses the disable macros when opening then the worksheet menu bar and other command bars are still available. I would like to hide all of the data sheets and display another sheet that would normally be hidden displaying a message that the macros have to be enabled for the program to work correctly if disable macros is chosen. When the enable macros are used I would like the Error page to be hidden.
View 6 Replies View RelatedI have a user that keeps a maintenance log in an Excel worksheet and sends an updated copy once a week to a board member. Two weeks ago, the board member started complaining that he was prompted to enable/disable macros on opening and became worried when my user stated that no macros were used in the book. He is now concerned that we have sent him a virus.
I know the file is clean because I've scanned it, and when I look at the file in VB, there are no modules or classes present just the Sheets 1-3 and the ThisWorkbook file. None of these objects have any code in them. My user does have some macros in PERSONAL.XLS but they are not used in the workbook in question.
No one else gets the prompt for enabling/disabling macros. Even if I set my security to prompt for any macros, I get no message. I'm convinced that there must be some setting in his Excel that is causing this individual to get this message. Is there anything else other than a macro that would cause this?
What is the commands or script for deleting a macro automatically using
another workbook macro.
Is there a way to hide a macro from the list where you choose which to run, but not in the VBA editor? The userbox I just created calls upon 2 different macros, and has a macro to bring up the userbox. I need a way to hide the macros in Module3 from selection, but keep the macros in Module4 available to choose to run.
View 9 Replies View RelatedI have two macros. Macros 1 opens another excel file, counts the number of rows it has, inserts that number of rows into the master file and then copies the data over. Key code as follows (x3 for 3 worksheets)...
For k = 1 To numrows
Selection.EntireRow.Insert
Next k
Macros 2 'cleans' the appended data by systematically going through each row, checking if certain cells have data, and if not deletes the rows. Key code as follows (x3 for 3 worksheets)...
For l = lastrow To 12 Step -1
If WorksheetFunction.CountA(Range("B" & l & ":C" & l)) = 0 Then
Rows(l & ":" & l).Select
Selection.Delete Shift:=xlUp
edelrows = edelrows + 1
End If
Next l
Here's the weirdness. macros 1 runs fairly quick the first time out (few seconds). Macros 2 takes about 2 minutes to run (which I'm fine with). However, when I run macros 1 AFTER running Macros 2, Macros 1 goes from taking a few seconds to a few minutes.
how to use excel 4 macros. I do not even know where to put them. I have inherited some spreadsheets that have both Xcel 4 Macros and vba macros in them. (Well, I get a warning that I cannot switch them off when I disable macros on opening a workbook). I cannot even find the offending version 4 macros so that I can re-code them in VBA.
A little background on what i am doing. I have a spreadsheet that tracks when i have blown the dust out of our computers. I have set up conditional formatting so that the text turns red after 300 days and the cell turns yellow after 600 days. However, some computers are in high dust environments. I would like the spreadsheet to tell me to blow the dust out of these sooner. see the attached sheet.
dust 1.xlsx
I'm trying to analyse all of my bank statenments to see where all my money went!! I've got online banking so I thought it would be a relatively easy process of copying and pasting each month into excel.
Well, that in itself was easy, the problem is with the formatting of the financial numbers. Excel doesn't seem to recognise them as numbers, so at the moment I can't do any manipulation with the numbers. I have tried everything such as:
Format Cells > Number > Number and Currency
Copy and Paste Special > Values only
Copying into Notepad and back into Excel
Even if it appears to have changed the numbers to 'number' or 'currency' formatting by right justifying the numbers, if I try to add up the numbers using the SUM function, it doesn't want to know.
For some reason nothing seems to work - what am I missing?
For an example, I have attached a snippet of the spreadsheet with my starbucks transactions.
Is there "code" for different formatting in a spreadsheet so you can use an IF statement to do something like:
if(A1=blue background,"Yes","No")
or
if(A1=red text,"Yes","No")
etc.?? But replace "blue background" and "red text" with some sort of number code? I want to compile a list of the items that are formatted with certain background colors and/or text colors and then organize only those items into a chart.
Any VBA that I can put into a macro that will convert conditional formatting into fixed formatting..? So when the cell contents/formulas are deleted the formatting remains. Assume that the range I want to convert is A1:D200...
View 9 Replies View Relatedhow do I install a macro from my laptop to another?
View 9 Replies View RelatedI'd like to be able to just open the spreadsheet up and have the macros already saved in there. Is there a way to save these macros?
View 2 Replies View RelatedI am currently volunteering for a small organisation. I have spent the last week designing a userform to be used by other staff members in their office. However, when I asked the manager 'Do you have Microsoft Office?', I got a very firm and definite 'yes' in response.
It turns out they are using LibreOffice, which is a free alternative. I have also tried to get my userform to work with Open Office - but cannot seem to get either to work.
I have changed the settings in both suites of software to 'Load/Save Basic Code' and enabled macros to run via the security options.
My userform is not that complex. I have limited knowledge of VBA and zero experience using Basic, so have come up stuck.
I need to write a macro whereby it queries a cell and if there is any value (it will be either blank or contain characters) in the cell a new row is to be inserted directly above the cell.
i have a matrix with all possible values.
i need to develop a macro, which will change the color of the cell according to the value of the cell.
for example if a specific cell in the matrix has a negative value then the macro should change the color of the cell to blue. if its value is positive then the macro should change the color of the respective cell to red.
I have an API that continuously gets stock price. The price appears in one cell at all times - let's call it A1
I want to record the value in a different cell every time it changes, so I can analyze the data.
so A1 = 15
B1 = 15
little later
A1 = 15.2
B2 = 15.2
I did some googling and came to a conclusion that I needed to write a macro.
I see that there is a function or event called Worksheet_Calculate()
Apparently it is supposed to do whatever when a value changes (per calculation)
How do I write a macro, and then execute it?
I've created an Add-in (.xlam file) in Excel 2007 and installed and activated it (it shows up under "Active Application Add-ins" on the Add-in menu for new workbooks).
I can see the macros and code when I go to the VBA editor, but the macros don't show up in the regular macro list.
I know that I can write code for the new workbook that will reference the Add-in code, but I want other users to be able to install the add-in and run the macros in it right away without any knowledge of VBA. Is there a way to make this possible?
I have created a excel spread sheet to be set up like a schedule, including multiple macros through the sheet, when I started it put the macros into "Module 1", now if I wanted to use this same worksheet and copy it and create multiple sheets in one file via the tabs does it make a difference where the macros reside? if it remains in "Module 1" it will only be in one spot will this create problems later on or should the each reside in the sheet objects.
View 9 Replies View Related