Hi,
Does anybody have a code snippet that applies superscript and italics to the last 3 characters of the active cell?
I am trying to automate the process of creating footnotes.
I don't suppose it would be efficient to search all cells in the active sheet for ones that contain (#) as the last three characters (where # is a number).
Thanks in advance,
Mike
Re: rounding to 2 dp
=round(105.3009,2)
Re: Run one of two macros based on cell's value
Perfect! Thanks very much.
Re: Hide a concatenated cell
The correct way to hide values using custom format is by implying nothing, separated by a semi-colon. Thus concactenated cells are text, and so the custom format ;;; - nothing for positive numbers, nothing for negative, and nothing for blanks or text.
Hope that helps,
Mike
Re: Run one of two macros based on cell's value
The Macros are used basically to format a picture from no fill, no line, to full fill, full line. (and back again)
UDFs would not be sueful here. I may have to make the Named cell driven from a userform, I think..
Re: Run one of two macros based on cell's value
Thanks - I knew I was close...
..but is it possible to have the macro run as if it was application.volatile?
i.e. - user enters number, and CrankTooMuch() runs all the time to evaluate the value in the cell named CrankLevel?
Can somebody possibly tell me what is wrong with this simple code?
Sub CrankTooMuch()
If CrankLevel < 6 Then
Application.Run "CrankDown"
Else
Application.Run "CrankUp"
End If
End SubIt should run the CrankUp macro if the cell named CrankLevel is equal to 6, and run the CrankDown macro if it is less.
The crankUp and CrankDown macros apply a one-off formatting change, and remove it respectively.
Thanks in advance,
Mike
In the formula, ensure you add the "false" for range lookup.
e.g. =VLOOKUP(A1,B1:C2,1,FALSE)
Yes. All very strange.
It appears that I have somehow tricked excel into redefining the range...
Does anybody have storage that I can post the file to?
I think I need a VBA solution, as I would like to do it for all sheets in the workbook.
Actually, the reason why is that I have noticed a problem/bug with hiding rows. Where all rows beneath a chosen area have been hidden, so that the available excel window is reduced - i.e. it doesn't scroll all the way to row 65536 - it doesn't seem possible to unhide these rows.
Example:
In the attached sheet, it is not possible to unhide any rows past 67, effectively limiting excel.
Anybody experienced this before or knows a fix?
**EDIT - Can't attach file as it is 75KB despite having nothing visible (cleared all formats and values) **
Hi,
Is it possible to unhide all rows and columns in a workbook?
Any suggestions gratefully received,
Mike
Simple Excel solution: (No macros)
If date (with leading ' ) is in cell A1
In cell B1
+RIGHT(A1,LEN(A1)-1)
That is absolutely perfect - thanks very much!
Hi,
I am struggling to write a macro which will copy the active sheet n times and rename it based on the n names listed in another sheet.
Has anybody done something similar?
Thanks in advance,
Mike
This custom UDF will do it:
Function SheetName(rAnyCell)
Application.Volatile
SheetName = rAnyCell.Parent.Name
End Function
Paste it into a new module and then gointo any cell and type
=SheetName(A1)
Mike
Is there a formula in the cell, but just nothing displayed, it may be simply the format of the cell.
Quick check: Highlight the cell and go to Edit > Clear > Format and if it becomes visible, it was probably conditional formatting, or custom formatting with a couple of ";" symbols in there..
HTH
Yes please.
=A1/$A$10 where A10 is the cell with the total in it.
To copy the formula, hit Ctrl C and paste it with Ctrl V.
Aah!
Thanks very much. I knew there must be a simple solution..
Does anybody know how to remove the leading tilde when entered into a cell in excel?
ie. cell says ~31-FEB-04 but I want the numeric format of the date.
Find and replace does not work, and =+RIGHT(B1,LEN(B1)-FIND("~",B1)) is just tedious.
Any suggestions gratefully received,
MIke