If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > PC based Database Applications > Microsoft Excel > MACRO Help

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
Join Date: Jul 2012
Posts: 13


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.
Reply With Quote
  #2 (permalink)  
Registered User
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.

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

Reply With Quote

excel, macro, vba

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On