Results 1 to 2 of 2

Thread: MACRO Help

  1. #1
    Join Date
    Jul 2012

    Unanswered: MACRO Help


    I have a code made for an array. It basically removes the suffix at then end of a part number. I now want to create a macro that can do this but I need some help. Below is the code:

    LEFT(A1,SUM(MATCH(TRUE,ISNUMBER(1*MID(A1,ROW($1:$2 0),1)),0),COUNT(1*MID(A1,ROW($1:$20),1)))-1)

    Any help is appreciated.

  2. #2
    Join Date
    Jan 2009
    Ohio, United States
    Here's some sample code that enters the formula =left(a2,5) into cell B2 and drags it down to the last record in the recordset.

    Sub FormulaEntryAndAutoFill()
        ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],5)"
       Dim rng As Range
        Dim rngData As Range
        Dim rngFormula As Range
        Dim rowData As Long
        Dim colData As Long
       Set rng = ActiveCell
        Set rngData = rng.CurrentRegion
       rowData = rngData.CurrentRegion.Rows.Count
        colData = rng.Column
       Set rngFormula = rngData.Offset(1, colData - 1).Resize(rowData - 1, 1)
    End Sub
    To get your formula in VBA format to insert in the code above, click to record macros, enter your formula into the table, hit enter then stop recording the macro. View your macro and copy the formula it comes up with. When I did for yours I came up with somethign like "=LEFT(R[-1]C[-2],SUM(MATCH(TRUE,ISNUMBER(1*MID(R[-1]C[-2],ROW(R[-1]:R[18]),1)),0),COUNT(1*MID(R[-1]C[-2],ROW(R[-1]:R[18]),1)))-1)"

    Hope this helps


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts