Posts by Luke M

    Re: VBA Help needed to tweak my Search and Find macro...


    like this?


    Re: Change values to the right of a decimal point


    You'd need a second piece of info, as either way you need someone of knowing if the input was just lbs, or lbs + oz. Let's say there was some indication in B2, you could do:


    =INT(A2)&"lb "&INT(MOD(A2,1)*IF(A2="Mixed", 100, 16))&"oz"


    So, if user inputs a mixed value (lbs + oz), then we change 140.15 into 140lbs 15oz. If user selects "single" (or whatever else you pick), formula outputs 140lbs 2oz (if you want 2.4 oz, remove the INT function on latter part)


    Edit: For clarity, I'd probably setup your data input to have two cells clearly marked, one for "Pounds" and the other for "Ounces", and then user can choose one or the other, or both. Then your unit conversions will be a lot easier.

    Hello all,


    I've written this script to loop over all the PivotTables in a workbook. Goal is that user can input a year, and all the tables will go to that selected year. I had previously been using a Slicer, but that doesn't appear to work on a Mac (client is using Excel 2015). I don't own a Mac, so I haven't been able to test in the environment. Anyway, this latest bit of code appears to be running for user, but the tables aren't getting updated. Any ideas?


    For workbook layout, you've got 3 sheets, each with at least one PT. All of the PivotTables contain a Date type field, which has been grouped by Months and Years. The Years field is set as a Report field in each PT.


    Edit: Cross-posted here: https://www.reddit.com/r/excel…manipulating_pivottables/

    Hi all,


    I wrote this to convert a selected range of cells into a string that I can paste into Reddit. Reddit tables need a format like



    Header1 | Header2 | Header3
    ---|---|---
    Value | Value | (empty)
    Value | Value | Value


    etc.


    where pipe symbol separate columns, and you use the 3 dashes to separate header row from body of table. This works, but seems like I've got some extra loops.


    Re: Copy a varying range


    Something like

    Code
    Cells(Start_Row, 2).Resize(Length_Row).Copy
    Worksheets("Temp123").Range("C6").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

    Re: left function compile error


    The fact that VBE didn't autocapitalize makes me wonder if you have a function/variable called "left", and it's getting confused?


    The only other thing that I see happen with causing Left/Right to fail is if you have a bad reference (usually when you've built on say Excel 2013, and then file gets opened in older version like Excel 2010).

    Re: Deleting multiple rows at once


    Try this


    Code
    Range("A" & x).Resize(100).EntireRow.Delete


    Resize method lets you state number of rows and/or column to resize to, using original cell(s) are starting point.

    Re: Compiled Error - Expected: Name parameter


    Hello, and welcome to the forum! :)


    Your line break is in wrong spot, can't end a line with comma and go to next line with using the " _" linking. Change to:

    Code
    ActiveWorkbook.SaveAs Filename:="C:\wamp\htdocs\sample_dos.csv", _
        FileFormat:=xlCSVMSDOS, CreateBackup:=False

    Re: Deleting multiple rows at once


    Glad you figured it out. See the 2nd block of code above for how to delete multiple rows. Or, if you truly want to "select some cells and delete them"...


    Code
    Selection.EntireRow.Delete

    Re: Deleting multiple rows at once


    When deleting, you'll want to count backwards, since the rows keep shifting. This is assuming you're doing something other than a delete, you would do


    Code
    For y = 1000 To 1 Step -1
    'Do other stuff
    Rows(y).Delete
    Next y


    If you truly just need to delete a block of rows

    Code
    Range("1:1000").EntireRow.Delete

    Re: Split worksheet into multiple worksheets with a two row header


    Changed a few things during the setup. This seems to work correctly.


    Re: VBA Preventing pasting from another sheet in same workbook


    Hi DezB,


    Ouch! Just kidding, I know it can be finicky. I think problem is that when you click the Reputation button, need to make sure you select the 'I approve' and not the 'I disapprove'. If you want, you could try again on this post...hopefully balance things out. =P


    Either way, have a good day, and keep on learning!

    Re: VBA Preventing pasting from another sheet in same workbook


    The activation macro always fires, and it makes a change to the sheet. As soon as you edit something, the clipboard gets cleared, so there's nothing for you to paste.


    Since the range being changes is constant, why do you need this macro? Looks like you should be able to just manually setup the formulas and forget about them.

    Re: Run time error 1004 application-defined or object-defined error


    First off, do you really want to apply this formula to entire column? That will be over a million calculations. Ultimately, that's also what's causing this to fail. A1 (the first cell in your range) will refer to row 10 cells with a relative reference, A2 refers to 11, and so on. When you get to end of column, the references go beyond the edge of the sheet, and you get an error.


    Side note, since you're really checking about which to use in VLOOKUP, you can simplify to something like:

    Code
    Sheets("QA").Range("A10:A20").Formula "=VLOOKUP(IF(LEFT(U10,4)=""auto"",AE10&AF10,AE10&AF10&AG10&AH10),IF(SUM(W10:AD10)>0,$AV$10:$AW$54,$AO$10:$AP$54),2,0)"