Posts by Simon Lloyd

Re: Undo Option Lost After Event Macro Code

To be honest the copy sheet method is better as you don't have to do all that tracking just copy the sheet and then use the following:
Create a macro that copies the worksheet and call it MyUnDoMacro, then at the bottom of your original macro add this line

Application.OnUndo "Undo My Macro", "MyUnDoMacro"

the Undo My Macro is what the user would see and the latter part calls the macro, now after your macro has run you just click on the undo as you would normally and hey presto macro reversed!

Re: Copy Information Selected In Listbox

Quote from wallar

....... 3 list boxes and copy this information into the excel sheet behind on the same row.

What do you mean same row on sheet behind? Userforms don't have rows so how can it possible be the same row?

Re: Password Protect User Entry

Will the worksheet be already protected? what does entering a password do?, what are you expecting to happen when a prompt for password is given?

Re: Macro Has Removed Undo Option

Whenever you change anything using VBA it clears the undo stack and VBA doesn't support an undo, the only way is to create a macro that reverses the effect of the previous macro or copy the worksheet and hide it and upon close delete it or have a button with macro to re-instate it!

Re: Vba Syntax For Named Ranges

This line should:

Range("D" & LastRow + 1).FormulaR1C1 = Sheets(Navn) & "=Navn!RiskYear"

should probably look like:

Range("D" & LastRow + 1).FormulaR1C1 = "=" & Navn & "!RiskYear"

ADDED BY ADMIN

Sheet1.Range("A1:A10").Name="MyRange"

Re: Copy Variable Range To Next Blank Row

You wouldn't want to cut and paste just add Rng.Clearcontents to the end of my code before end sub. as for the others highlight them in the VBE then hit F1

Re: Conditional text deletion from cell

Rory i assumed it wasn't #N/A as thats the result of a formula that hasn't got any alues to work with and can be cured at sheet level with the ISNA function within the formula.

Re: AutoFill Down Rows Based On Another Columns Last Cell

can you post a dummy workbook?

Re: Conditional text deletion from cell

Better to use excels find and replace, far more efficient!

Sub Clear_Found_Cells()


    Cells.Replace What:="N/A", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

Re: Macro Autofill Down Varibale Number Of Rows

Try this:

Sub Macro1()
Dim Rng As Long, Rng1 As Range
 Rng = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row' the range of entries
   With Sheets("Sheet1")
    .Range("E1:E" & Rng).FillDown ' the range where the formula is in the first cell
    End With
End Sub

Re: Macro To Copy To Next Blank Row

Try this:

Sub Move_New_Account()
Dim Rng As Range, MyCell As Range, Rng1 As Range
Set Rng = Sheets("Account Input").Range("A2:A" & Range("A" & Rows.Count).End(xlUp).Row)
Set Rng1 = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Rng.EntireRow.Copy Destination:=Rng1
End Sub

Re: Vba Code To Open A New Workbook

Try this:

Sub Save_Book()
Dim MyBook As String, MyRange As Range
MyBook = ThisWorkbook.Name
Set MyRange = Workbooks(MyBook).Sheets("Sheet1").Range("A1")
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & MyRange.Value '_
       ActiveWorkbook.Close
End Sub

Re: Count Uniques In Visible Cells

It can be done with formula, for your number of batches use =SUBTOTAL(3,B2:B100) and for your Subtotal use =SUBTOTAL(9,B2:B100)

Re: Auto-resize Columns Without Unhiding All Columns

Private Sub Worksheet_Change(ByVal Target As  Range) 
Application.ScreenUpdating=False
    With Me.Cells 
        .Columns.AutoFit 
    End With 
Range("A:C").EntireColumn.Hidden = True' where coulmns A to C are hidden
Application.ScreenUpdating=True
End Sub

Re: Automatically Increment Cell Once Per Day

Quote from Mikerickson

VB is not needed. Put =TODAY()-39570 in a cell and format it General. The value in that cell will increase every day.

Only if the workbook is opened every day or refreshed everyday!

Re: Subtract Or Add A Specific Amount Daily

I agree!

Re: Subtract Or Add A Specific Amount Daily

Bob, whilst i understand what you are doing there, there isn't a facility to do it daily without any manual intervention, if the workbook is left open or opened every other day or more the subtraction will cease to be daily!

Re: Subtract Or Add A Specific Amount Daily

You will have to use the OnTime method!

Re: Send Value To Specific Cell

Marius, i haven't looked at your attachment, as per your first request you said that if you enter a number in any cell within a specific range (in my example the range was C1:G20) then the row number of that cell should be entered into the cell number of the entry and that should appear in column Z (in my example A).

So, in my example if you enter 5 in lets say C2 then A5 will display 2 (meaning row 2), this is what i took your request to mean!

Can you clarify?

Re: Merge 5 Columns Into 1

So you don't really want to merge you want to copy all 5 columns to one?