Posts by michael123

  • Hide all sheets - but remain on current sheet

    Hi guys,


    A pretty simple one here..


    I have the code to hide sheets, but I want to be able to remain on the sheet where the macro button was pressed:



    The same applies for my Unhide all

    Code
    Sub UnhideAll()
    Dim wsSheet As Worksheet
    For Each wsSheet In ActiveWorkbook.Worksheets
        wsSheet.Visible = xlSheetVisible
    Next wsSheet
End Sub



    Thank you

  • Copy cells from one workbook to current workbook (the one running the macro)

    This is a simple one, but ive been searching forums for ages (maybe i've been searching the wrong thing)


    The workbook I am copying from is fine to access.

    It has already been defined as a workbook that is now called wbopen

    for example

    Code
    Dim wbopen As Workbook
 
set wbopen = workbooks.open('cannot post files actual location)
With wbopen.Sheets("sheet1")
    Range("a5").Copy



    However, I just cant seem to figure out how I can use my current workbook to paste back into.

    surely its simple?

    something like activeworkbook.name?

    I should stress that the workbook i want to paste into is already open (as it was used to run the macro)

    For ease of explaining, lets say I want to copy cell "A5" from wbopen and paste it into "A1" in my current work book that im running the macro on

    Many thanks in advance

  • Speed up VBA code

    Re: Speed up VBA code


    try adding (I see you have already used the screen updating trick):

    before code:

    Code
    Application.DisplayAlerts = False
Application.Calculation = xlCalculateManual
Application.ScreenUpdating = False



    after code:

    Code
    Application.DisplayAlerts = True
Application.Calculation = xlCalculateautomatic
Application.ScreenUpdating = True

  • userform that returns specific cell

    Re: userform that returns specific cell


    Sorry maybe I shouldnt of posted that first bit of code, I just wanted a way to replace "D3" for example, with a user input.

    However...you have helped me solve my problem :) (I wasnt using text after textbox1)

    Thank you for the help and quick reply

  • userform that returns specific cell

    I have created a userform (but I dont fully understand them so no point posting my userform code)

    The userform has two input boxes. Because of the lack of code I will call them textbox1 and textbox2.

    textbox1 is for the user to enter the column letter

    textbox2 is to enter the row number

    I should also mention the userform has two buttons, "OK" and "Cancel". My "Cancel" button works and exits the userform/sub.

    Again for the lack of knowledge I will refer to these buttons as Commandbutton1 and Commandbutton2

    My overall aim is that the following code takes a range reference according to what has been entered in the userform.


    Code
    -------------rough example---------------
 
'autofill to last row
    LastRow = Cells(Rows.Count, "q").End(xlUp).Row
    Range[B]("D3").[/B]AutoFill Destination:=Range[B]("D3:D"[/B] & LastRow)
 
'copy and paste special values
ActiveSheet.Calculate
Range([B]"D3:D"[/B] & LastRow).Copy
 ActiveSheet.Range[B]("D3").[/B]PasteSpecial xlPasteValues




    For example...I want to create something like

    Code
    dim inputcolumn as integer
dim inputrow as integer
 
inputcolum = textbox1
inputrow = textbox2
 
range("inputcolumn" & "inputrow").value........etc (as above code)




    So basically whenever theres a reference in my code to a cells column or row (in example, "D3") it takes the value from the userform box.

    I know the question is quite messy/vague.

    Can anybody help me?

    Many thanks in advance

  • Speed up slow calculation in vba

    FYI


    After much debating and taking into account the helpful responses. I have re-designed my code to the following:




    As most can see it pastes the formula into the first cell in the column.

    Autofills that formula to the last row.

    Copys the cells and then paste special the values back.

    displays a "elapsed time" box

    COMPLETION TIME: 20sec approx (for over 130,000 rows)




    Once again many thanks for all your posts, they have been a great help to me!

  • Execute macro only on visible cells after filtering

    I've been stuck on this one for ages...

    I have alot of data, I want to apply a macro to only the relevent rows to save calculation speed. Simple.

    I have a working autofilter so that parts not the issue

    Through my extensive (banging my head aginst the computer) research, It seems the best way to do this is to name a range for the filtered data and apply the code.

    I want the range to be variable, from A3-AK"last row of data" (Endx1up NOT down)

    the output will be in column AD

    Code
    '---------Code that has already auto filtered ----------
 
'Want: code that selects and names the range
 
'Want: a way of applying the code i already have to these cells
 
`--------- my code ------------




    So in summary, I need (please):

    1.A way to name a variable range

    2.A way to apply my code to this variable range

    3.Results in colunm AD (but this one im sure i can figure out for myself)



    THANK YOU!!!!!!!!

  • Speed up slow calculation in vba

    Re: Speed up slow calculation in vba


    UPDATED CODE

  • Speed up slow calculation in vba

    Re: Speed up slow calculation in vba


    Hi Rob,

    Basically I wanted to find a way to calculate mduration quickly for over 130,000 cells, although it takes a long time (at the moment). It has to run off a button so that other users (who are not familular with the mduration calculation) can easily calculate it, or be it have a long coffee break while it is.

    Im looking into autofiltering so that it can run faster and applying the suggestions from "AAE", but for me it's a slow process as i'm very new to vba.

    Thank you for taking the time to help (I have changed the names as suggested, many thanks)

  • Speed up slow calculation in vba

    Re: Speed up slow calculation in vba


    Thank you both for your quick replies and help!

    cytop:
    I have changed the coding as you've said and am now looking into With, End With...so thank you!!

    AAE:
    It has to run using code. Im looking into special cells and filtering. So thank you aswell!!



    If I manage to reduce the time i will update the post so you can (hopefully) approve.

    Once again thank you both for putting the time in to help!


    :)

  • Speed up slow calculation in vba

    Re: Speed up mduraiton calculation in vba


    Sorry i posted a messy code.............here is a neater version with very slight (but irrevent) bits taken out.................


  • Speed up slow calculation in vba

    This is my first code ive written in vba so I apologise in advance...


    My problem is this...
    I have created a macro that calculates mduration, it take the relevent inputs and plugs it into the macro version of mduration (a function). The macro runs fine all the way through. However, it takes about 13mins to complete!!!


    I have tried everything that I could think of but alas my knowledge of vba is not good enough.


    My question is:
    Can anyone help me find a way to reduce this speed, in an ideal world to under a minute.



    IMPORTANT THINGS TO BE AWARE OF:
    - The this macro runs on 130,000 rows!
    - The only way i could think to create this macro is with a loop (suggestions welcome)


    I have used alot of "offset" in my coding so the actual columns are the following;


    AD = where i want the results to appear
    AK = coupon
    K = NAV
    E = Nominal
    AB = Mdate
    Q = asset type
    AC = maturity



    Once again this is my first code and first post so its not perfect.


    Thank you!!!!