MACRO Help

aftermarket_auto · #1 (**permalink**) 11-21-12, 09:12

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.

scrtchmstj · #2 (**permalink**) 12-18-12, 10:50

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

Internet Services

Web Development

Digital Marketing

Consumer Tech