Posts by gers1978

  • VBA: Named Argument Not Found error

    Ok, this code works fine:


    Code
    detailsSheet.Cells.Copy
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues


    But conscious that using Selection can be slower, I tried:


    Code
    detailsSheet.Cells.Copy
detailsSheet.PasteSpecial Paste:=xlPasteValues


    This gives me a compile error, "named argument not found".
    (details.Sheet is set elsewhere, on a user form, as a worksheet)



    Also posted here: http://www.mrexcel.com/forum/e…nd-error.html#post4548758

  • Application.ScreenUpdating = False not working properly

    I call a function right at the start of my code that looks like this:


    Code
    Sub OptimizeCode_Begin()
Application.ScreenUpdating = False
EventState = Application.EnableEvents
Application.EnableEvents = False
CalcState = Application.Calculation
Application.Calculation = xlCalculationManual
PageBreakState = ActiveSheet.DisplayPageBreaks
ActiveSheet.DisplayPageBreaks = False
End Sub


    At the end of my code I call:


    Code
    Sub OptimizeCode_End()
ActiveSheet.DisplayPageBreaks = PageBreakState
Application.Calculation = CalcState
Application.EnableEvents = EventState
Application.ScreenUpdating = True
End Sub


    In one of the subs that runs in between, I have the following code:


    Code
    If .Worksheets(intCount).Name = "Invoice" Then
                Worksheets("Invoice").Delete
                .Worksheets.Add
            With ActiveSheet
                .Name = "Invoice"
            End With
                booFound = True
                Exit For
            End If


    Yet for some reason, at the line:


    Code
    Worksheets("Invoice").Delete


    I still get the warning about deleting a sheet.


    If I change that block above to:



    It works fine. But my question is why do I need the "Application.DisplayAlerts = False" immediately before the .Delete statement? Surely alerts should already be set to False due to the initial sub setting it that way?


    Also posted here:


    http://www.mrexcel.com/forum/e…properly.html#post4548740

  • Why is Option Explicit optional?

    Everywhere I look, advice is that declaring of variables is a good thing and that use of Option Explicit is highly recommended.


    What I can't fathom out is why is it optional? Is there any down side to using it?

  • Better way of applying a formula down a column in VBA?

    I used the macro recorder to get the initial code and incorporated it into the following:


    I'm thinking the line beginning "ActiveCell.FormulaR1C1" is not the best way to do this?
    I'm trying to tidy up the code as best I can



    Also posted here:


    http://www.mrexcel.com/forum/e…ications.html#post4507123

  • SUM not working in certain cells only

    Ok, I have a table with 10 rows. Each row is pulled through from the bottom row of another workbook. I have 10 such workbooks, and this table pulls through the bottom row from each to make a sort of "summary" table.


    It used INDEX MATCH to get the data, and pulls it through just fine. In all 11 workbooks (10 + summary) the tables are in A4:Z11.


    I have a totals row in my summary table, and I noticed that in column R, the total is 0. All other column's total row is correct.


    R4:R9 are indeed 0, which is correct, but R10 is pulling through £12,531 and R11 £112,137, again both correct. So surely the total row for R should be £124,668?


    Now what's weird is when I select R10 and R11, the little SUM you get at the bottom status bar of Excel says 0. The COUNT is correct, saying 2, but SUM says 0. If I copy and paste values on R10 and R11, that little SUM in status bar still says 0.


    However if I manually type 12531 and 112137 into R10 and R11, the SUM at the bottom (and the totals row of the table) add up correctly.


    What's even weirder is if I select Q10 and Q11 or P10 and P11 (which all have non zero numbers in), the SUM at the bottom status still says 0 (again, the COUNT is correct) but in these columns the totals row is adding just fine anyway!


    The formula for every cell in my summary table is:


    Code
    =INDEX(path\[Workbook.xlsx]Sheet1!$A$4:$Z$11,MATCH($A5,'path\[Workbook.xlsx]Sheet1!$A$4:$A$11,0),MATCH(R$4,path\[Workbook.xlsx]Sheet1!$A$4:$Z$4,0))


    (obviously the lookup reference changes in each cell)


    Also posted here: http://www.mrexcel.com/forum/e…lls-only.html#post4504307

  • #REF error when referring to external workbook

    I have a workbook with a sheet that makes use of INDEX and MATCH to look up values in another workbook.


    Problem is I always get #REF errors when I open it. I assume this is because the external workbook the formulas refer to is closed, cos when I open said workbook, it works fine.


    I tried using VLOOKUP instead, same problem.


    Is there any way at all round this?


    I'm using Excel 2010.


    Many thanks.


    Also posted here: http://www.mrexcel.com/forum/e…workbook.html#post4500452

  • Insert row into table, and copy formulas, etc, from previous row

    I have a worksheet (attached) with 3 tables. I want a button that adds a new row to each table (or 3 buttons, one for each table, doesn't matter). But when the new row is inserted, I want it to inherit the formulas, etc, from the row above.


    Also, everything else on the worksheet needs to be adjusted accordingly, so other cell references in formulas, etc, are not screwed up.


    Thanks!


    Password for protected sheet is password


    Question also asked here: http://www.mrexcel.com/forum/e…ious-row.html#post4456866

  • Runtime 424 "Object required" error

    I have some code and one of the subs contains this line:


    Code
    Do Until Sheet1.Range("A" & Row).Value = vbNullString


    It works flawlessly.


    However I now want to run this code on a different sheet, namely one called "Invoice". So I changed the code to:


    Code
    Do Until Invoice.Range("A" & Row).Value = vbNullString


    However now when I run it, I get a runtime 424 error, "Object Required".


    If I change it to:


    Code
    Do Until Worksheets("Invoice").Range("A" & Row).Value = vbNullString


    again, it works fine.


    Can anyone tell me what's going on, and why sometimes you need to use "Worksheets("<name>")" format and sometimes not?


    Thanks!


    Also posted here: http://www.mrexcel.com/forum/e…ed-error.html#post4324572

  • Clever way of formatting names nicely?

    I have a column with lots of names, and I want to format them nicely using VBA. I've been trying to use formulas in a spare cell using LEFT, RIGHT, MID, FIND, ISERROR, etc, to get the desired result, then copy/paste the resulting formula into my VBA code, but it's getting very unwieldy, long and hard to follow.


    Basically the names as it stands can be anything from:


    SMITH, JOHN M.
    JONES
    TAYLOR, CHRISTOPHER (Chris)
    BAKER, MIKE
    SMYTH, EDWARD E. (Ted)
    MARK.MILLER
    PAUL PARKER


    I want to format them so that they become First Initial. Surname, unless there's only one word, then I want it left as-is. And I need the whole thing converted to Proper case.


    So in the example above, the finished result should be:


    J Smith
    Jones
    C Taylor
    M Baker
    E Smyth
    M Miller
    P Parker


    Thanks!


    Also posted here:


    http://www.mrexcel.com/forum/subscription.php?folderid=0


    http://www.excelforum.com/exce…matting-names-nicely.html

  • Using VBA to dynamically use filters

    I have the attached spreadsheet and I've created some code to create a PivotTable, then format some names, then copy the data to a new sheet.


    What I'd like to do is instead of copying all of the data to a single new sheet is create a new sheet per company name, and copy just that companies data to that sheet.


    I can obviously record a macro of me doing this for each company by using the filter in the PivotTable, but the resulting code from the macro is not dynamic, i.e. each time I do this the company name is "hard coded" into the code. This is not use if, next month for example, the data changed and there are more/less/different companies to work with.


    Is there any way of making this "dynamic"? So that each month, regardless of the data set, I can just click a button to execute the "RunAll" macro and it does what I'm looking for?


    Many thanks!



    forum.ozgrid.com/index.php?attachment/67154/forum.ozgrid.com/index.php?attachment/67154/

  • Formula doesn't work when run as code in a macro

    Re: Formula doesn't work when run as code in a macro


    Quote from S O;757403

    Can you show the whole code?


  • Formula doesn't work when run as code in a macro

    I spent a while creating a formula to format some names in column A the way I want them, and it worked fine. The formula is:


    =IF(ISERROR(UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1)))),MID(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)+1,1) & " " & LEFT(A1,IF(ISERROR(FIND(",",A1)),FIND(" ",A1),0)-1),UPPER(LEFT(RIGHT(A1,LEN(A1)-(FIND(", ",A1)+1)),1)) & " " & PROPER(LEFT(A1,(FIND(", ",A1)-1))))

    When I enter it, then fill/copy it down my column, it works a treat.
    However I then recorded a macro of me doing it, and when I look at the code afterwards, it's changed to:

    Code
    "=IF(ISERROR(UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1)))),MID(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)+1,1) & "" "" & LEFT(RC,IF(ISERROR(FIND("","",RC)),FIND("" "",RC),0)-1),UPPER(LEFT(RIGHT(RC,LEN(RC)-(FIND("", "",RC)+1)),1)) & "" "" & PROPER(LEFT(RC,(FIND("", "",RC)-1))))"


    and when I run said macro, the whole column has #VALUE errors.
    Any ideas? Thanks!