Posts by acat

    Code below is slow and gives error (closed the dialog box, don't want to get stuck again, but it was on last line)
    Need fast performance on 20000 + rows
    Trying to just copy column with formulas and paste as values in same place


    Code
    1. Sub FastPaste()
    2. Dim r1 As Range, r2 As Range
    3. Set r1 = Sheets("Sheet1").Range("G1:G20000")
    4. Set r2 = Sheets("Sheet1").Range("G1:G20000")
    5. r1.Copy r2.Value
    6. End Sub

    Code below applies filter to show only TRUE results, but it's tied to row 2 and column M
    Need a way to have this code be flexible by reading ActiveCell row and column address, not be hard-coded to M2


    Code
    1. Sub ShowTRUE()
    2. With ActiveSheet
    3. lastRow = .Cells(.Rows.Count, 13).End(xlUp).Row
    4. .Range("A2:M" & lastRow).AutoFilter Field:=13, Criteria1:="TRUE"
    5. End With
    6. End Sub

    20,000 rows of data


    Need VBA to identify matching offsetting amounts (e.g. 500 and -500)
    But match has to be found nested within first Vendor Number, then PO#, then Project#, then Date added and only then by Amount
    Identifying matches by placing corresponding row number in a helper column would be ideal, so if 500 is in row 2 and matching -500 is in row 1132 both get “2” in helper column
    After macro is done, filtering to blanks in helper column would show only the amounts without matches that need to be researched

    Problems encountered so far in trying VBA or formula “solutions” have not seem to produce reliable or workable results:

    • No nested solutions
    • Had so stop macros running on ~18,000 rows after ~10 minutes, just to find out it only went through ~2,200 rows, so speed is relevant
    • Matching offsetting amounts were missed, reliability is relevant


    Maybe Excel is not meant for this task at all… Would be nice though

    Ideas, thoughts?

    This code you proposed just selects the column and without ignoring cells with formulas which return blanks,
    whereas needs to select range C:Q and use column in which there's active cell as one determining how far down selected range will be (ignoring cells with formulas which return blanks)


    Code
    1. Sub Main()
    2. If Selection.Cells.Count > 1 Then
    3. MsgBox "Please select one cell in Columns C:Q", vbCritical, "Selection Error"
    4. Exit Sub
    5. End If
    6. If ActiveCell.Column >= 3 And ActiveCell.Column <= 17 Then Range(ActiveCell.End(xlUp), ActiveCell.End(xlDown)).Select
    7. End Sub



    The code below does select range C:Q, but all the way down as far as formulas go, without ignoring cells with formulas which return blanks



    Code
    1. ActiveSheet.Range("C1").CurrentRegion.Columns("C:Q").Select

    How do I modify below code line to:


    1. Limit the columns to only C:Q
    2. Determine how far down to select based on data in column F (ideally, I'd like for VBA to pick column based on which column has a cell selected in it, but no input boxes)
    3. Exclude cells with formulas, but returning blanks


    Will keep the code in Personal macro workbook and run on active sheet


    Code
    1. ActiveSheet.UsedRange.Select

    trying to have cell value change based on another cell value change


    code below throws this error


    [ATTACH=JSON]{"data-align":"none","data-size":"large","data-attachmentid":1221701}[/ATTACH]


    Code
    1. Private Sub Worksheet_Change(ByVal Target As Range)
    2. Dim intersection As Range
    3. Set intersection = Intersect(Target, Worksheets("Sheet1").Range("G1"))
    4. If Not intersection Is Nothing Then
    5. If Worksheets("Sheet1").Range("G1").Value = Worksheets("Sheet20").Range("K2").Value Then
    6. Worksheets("Sheet1").Range("F2").Value = Worksheets("Sheet20").Range("I2").Value
    7. End If
    8. End If
    9. End Sub

    I'll have Excel files from a folder listed on spreadsheet in column A6 & down
    Will have right click trigger in column B


    Need VBA to Save Excel file to another folder as .PDF
    Source path is in A3
    Destination path in B3
    Source full path is A3 & A in ActiveRow
    Destination full path is B3 & A in ActiveRow, but as .PDF, not Excel


    Excel in Source folder needs to be replaced with .PDF in Destination folder, i.e. don't need to keep Excel file