Results 1 to 2 of 2

Thread: MACRO Help

  1. #1
    Join Date
    Jul 2012
    Posts
    13

    MACRO Help

    Hey,

    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
    Location
    Louisiana, United States
    Posts
    163
    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.

    Code:
    Sub FormulaEntryAndAutoFill()
    
        Range("b2").Select
        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)
        rngFormula.FillDown
    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

    Joshua

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
  •