+ Reply to Thread
Results 1 to 11 of 11

Converting excel formula to VBA

  1. 03-16-2011, 04:59 PM #1
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Converting excel formula to VBA

    Hi all, I use INDEX formula but am having hard time converting this to VBA.. I am trying to automate this with a macro...Would anyone be able to help with this? Would be greatly appreciated!

    The formula I use below gives me results in my Summary tab C3 cell which then I need to be dragged down all the way to last row. I already define last row as ( LastRow = Range("A10000").End(xlUp).Row)

    the formula I use in Excel is:
    Please Login or Register  to view this content.

    My "Template.XLS" has 2 tabs: tab 1) Summary and tab 2) Mapping

    basically the formula takes E1 in my Summary Tab matches it with number in list A2:A11(Mapping tab) and finds the corresponding item in C2:C11(Mapping tab) and brings it in my cell in Summary Tab.

    I tried below but it won't go

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  2. 03-16-2011, 05:39 PM #2
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,528

    Re: Converting excel formula to VBA

    Try (untested):

    Please Login or Register  to view this content.

    You could probably just as easily use VLOOKUP rather than the INDEX/MATCH combo.

    It would help others to help you if you posted a sample workbook with some typical data.

    Regards
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke

    Reply With QuoteRegister To Reply
  3. 03-16-2011, 05:51 PM #3
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting excel formula to VBA

    avoid activate and select in VBA
    and this syntax isn't correct
    Range("C3:C" & LastRow).Select.FormulaR1C1

    use instead:
    Please Login or Register  to view this content.
    integrated:
    Please Login or Register  to view this content.
    Last edited by snb; 03-17-2011 at 04:08 PM.

    Reply With QuoteRegister To Reply
  4. 03-16-2011, 06:37 PM #4
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Re: Converting excel formula to VBA

    thanks for the responses all..

    Snb.. sorry I am little unclear on your posting.. do you mean it should look as per below?

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  5. 03-16-2011, 06:56 PM #5
    snb
    snb is offline
    Forum Expert snb's Avatar
    Join Date
    05-09-2010
    Location
    VBA
    MS-Off Ver
    Redhat
    Posts
    5,649

    Re: Converting excel formula to VBA

    You can even omit :
    Please Login or Register  to view this content.
    so the oneliner suffices.
    Reply With QuoteRegister To Reply
  6. 03-16-2011, 11:05 PM #6
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Re: Converting excel formula to VBA

    Hi snb. thanks for your help and time with this... unfortunatelly I get an error.."Run Time Error 1004" and Application Defined or Object Defined Error...

    the formula I have in excel is:
    =INDEX(Mapping!$C$2:$C$11,MATCH(E1,Mapping!$A$2:$A$11,0))

    I hope i explained it properl.. basically..I start on Cell E1 and need to to be dragged down to whatever last row is on the spreadsheet..

    and tried incorporate in below

    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  7. 03-17-2011, 12:25 AM #7
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Re: Converting excel formula to VBA

    attaching a worksheet hopefully that will make it more clearer...any help would be super appreciated! I feel little stuck on this...thanks so much
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 03-17-2011, 02:04 PM #8
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Re: Converting excel formula to VBA

    Hi, was wondering if anyone has any feedback.. Really struggling with this and can't seem to figure it out..

    Thanks so much

    Dan
    Reply With QuoteRegister To Reply
  9. 03-17-2011, 03:52 PM #9
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,528

    Re: Converting excel formula to VBA 

    Please Login or Register  to view this content.

    Regards
    Reply With QuoteRegister To Reply
  10. 03-17-2011, 04:26 PM #10
    donyc
    donyc is offline
    Forum Contributor
    Join Date
    08-31-2010
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    204

    Re: Converting excel formula to VBA

    Thanks so much TMShucks..I used the below but it looks like the formula does not get dragged down to the last available row in the sheet.. is there a way to tell it to go down to the last possible row within columns in the sheet?

    I have previously used below, is there a way to incorporate that?

    Dim LastRow As Long
    LastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 03-17-2011, 06:25 PM #11
    TMS
    TMS is offline
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,528

    Re: Converting excel formula to VBA

    The revised formula, based on snb's code, fills cells A1 down to the last row *used* based on the entries in column C. This is because, presumably, at the outset, there are no entries in column A other than the heading. I'm not sure why you would want to put the formula in any other cells, let alone "down to the last available row in the sheet", as that would simply increase the calculation time unnecessarily.

    There's no point trying to calculate the last row, you might just as well use cells.rows.count which, in Excel 2003, would be 65536.

    It would also mean that printing the worksheet would generate hundreds of pages of rubbish.

    Does the formula that I provided put the correct formula in every populated row? If so, is that not what you want? If it's not what you want, please describe that requirement.

    If you want to put the formula in every cell in column A, you might as well just say:

    Please Login or Register  to view this content.

    If that's not what you want, I have to admit to being totally confused ;-)

    Also note that using the full descriptor means that, if you rename the workbook, you will get an error, subscript out of range.


    Regards
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1