Posts by MisterT

Re: A macro to insert a module?

Thanks Roy,

I guessed it wouldn't (or shouldn't!) be simple.

Obviously it would be an unprotected workbook, but in practice, is it easier to generate a message box with instructions and the code to copy?

Re: Compare with spaces

=trim(a1) where FRED (or FRED_) is in cell A1

Re: Rounding up several numbers

=roundup(d5,0)

Hi,

Is it possible to (simply) get a macro (in an add-in) to insert a module into the active workbook, and then paste code in?

I realise this may be open to abuse, so may not be possible, but I am trying to use it to "idiot-proof" the macro inserting process...

Re: Define range based on xlDown

Brilliant, Aaron.

Thanks very much!

Re: Define range based on xlDown

Yes, I need to define it as it will be referenced as a named range in another macro to be used later.

I am happy for it to overwrite an existing range of the same name, as the data grows.

Re: Define range based on xlDown

Thanks for your speedy replies,

This is actually part of a bigger macro, and I need to do this in VBA. I understand the offset method of dynamic ranges in excel, but just need to know the VBA syntax.

Anybody?

Quick question:

How can I define a range and make it dynamic?
I want to define it based on the selection from the active cell down, using xlDown? (I don't know how large the range will be each time)

ActiveWorkbook.Names.Add Name:="Whatever", RefersToR1C1:= _
        "=Sheet1!R14C6:R15C6"

Effectively, I want to make the references R14C6:R15C6 be Activecell:Selection.xlDown.Select

Any assistance on syntax appreciated!
Mike

Re: Calculation help

Attached is a working example, which follwos Roy's sums, but formats the output.

Re: Calculation help

For clarification:

Is the date and time exactly as shown, i.e. in that exact format of
"m/dd/yy hh:mm" all in one cell.

I assume this is data generated from some kind of report, rather than typed in by hand?

Re: Copy Paste Values Worksheets to New Workbook

Ranger,

Thanks - For some unknown reason, it works when fired using Alt-F8, but not with the assigned shortcut key (then it only pastes the active sheet) ...

I will scrub the macros to a text file, re-paste, and see if that fixes it.

Thanks again,

Mike

EDIT: Found a similar macro in the personal.xls workbook had same shortcut assigned. Works perfect now.

Hi,

I'd appreciate a pointer on what argument I'm missing to:

copy selected (multiple) sheets, then paste values to a new workbook containing those (multiple) sheets:

Sub PasteSheetsValues()
'
' Copies and Pastes Selected Sheets as Values to New Workbook


    Dim w As Worksheet
    ActiveWindow.SelectedSheets.Copy
    For Each w In ActiveWorkbook.Sheets
        With w.UsedRange
            .Value = .Value
        End With
    Next w
End Sub

Currently, this only pastes the active sheet, not all the selected sheets. Am I missing something very simple?

Thanks in advance,

Mike

Re: Toggle Format

Thanks Matt,

That works perfect!

Mike

Can anybody tell me what is wrong with this? I must be missing something very, very simple...

Sub BlueShading()


If Selection.Font.Color = RGB(255, 255, 255) Or Selection.Font.ColorIndex = -4105 Then 'White or Automatic
    
    Selection.Font.Color = RGB(0, 0, 0)
    Selection.Interior.ColorIndex = xlNone
    Selection.Font.Bold = False


ElseIf Selection.Font.Color = RGB(0, 0, 0) Then 'Black


    Selection.Font.Color = RGB(255, 255, 255)
    Selection.Interior.Color = RGB(0, 28, 92)
    Selection.Font.Bold = True
    
End If


End Sub

Re: Fixing Colour Palette

Now it gets wierder...

I just scrubbed the file down to one sheet and removed all numbers (to reduce file size).

Now when I open it, my colour palette overrides the one in that sheet, so their "nice" green palette gets converted to mine.

I think this is an excel rather than a workbook problem. Baffling, and very annoying.

Sadly the file is still too big to attach. (even removing all formatting and values)

Re: Fixing Colour Palette

The wierd thing is, there is no code in their sheets. No modules, and nothing in the pricvate module...

Any thoughts?

Hi,

Every time I open a particular workbook it changes the colour palette to a lurid green for all open workbooks. Even if I then close the offending workbook, all others remain the same disgusting colour.

Does anybody know how to return to the default colour pallette?

Thanks in advance

Mike

Re: Italicise and superscript last 3 characters in active cell

Thanks for the help, Norie.

The code below now serves my needs very well, although it doesn't handle errors if there is no "(" in the cell. I can live with that.

Sub Footnote()
Dim I As Integer
 
 ' get the position of ( in active cell
I = InStr(ActiveCell.Text, "(")
 
With ActiveCell.Characters(Start:=I, Length:=I + 2).Font
.Superscript = True
.FontStyle = "Italic"
End With
End Sub

Re: Italicise and superscript last 3 characters in active cell

Thanks again, Norie.

Attempting to make this flexible enough to deal with double-digit footnotes, I wrote the following, which is not quite right. A correction would be gratefully received!

Sub Footnote()
With ActiveCell.Characters(Start:=Len(ActiveCell) - Find("(", ActiveCell) + 1, ).Font
    .FontStyle = "Italic"
    .Superscript = True
End With
End Sub

Re: Italicise and superscript last 3 characters in active cell

Perfect, except it gives subscript, rather than superscript. Eaily fixed (below)

Sub Footnote()
With ActiveCell.Characters(Start:=Len(ActiveCell) - 2, Length:=3).Font
    .FontStyle = "Italic"
    .Superscript = True
End With
End Sub