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

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
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.

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
Reply With Quote
Reply

Tags
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