Posts by michael123

    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

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

    Thank you

    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

    Dim wbopen As Workbook
    set wbopen ='cannot post files actual location)
    With wbopen.Sheets("sheet1")

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

    surely its simple?

    something like

    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

    Re: Speed up VBA code

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

    before code:

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

    after code:

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

    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. have helped me solve my problem :) (I wasnt using text after textbox1)

    Thank you for the help and quick reply

    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.

    -------------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
    Range([B]"D3:D"[/B] & LastRow).Copy
     ActiveSheet.Range[B]("D3").[/B]PasteSpecial xlPasteValues

    For example...I want to create something like

    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


    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!

    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 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!!!!!!!!

    Re: 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)

    Re: Speed up slow calculation in vba

    Thank you both for your quick replies and help!

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

    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!


    Re: Speed up mduraiton calculation in vba

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

    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.

    - 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!!!!