+ Reply to Thread
Results 1 to 5 of 5

VBA Syntax Problem

  1. #1
    Registered User
    Join Date
    05-17-2012
    Location
    Lexington, KY, USA
    MS-Off Ver
    2010
    Posts
    3

    VBA Syntax Problem

    Help I am going nuts. I have been looking at this line of code for hours and changed it around everyway I can think of and still it won't run.

    What am I doing wrong?

    Range("Address(Match(0,B2:B198,0),2):Address(Match(0,AF2:AF198,0),32)").Select

    Thanks,
    Gregg


  2. #2
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: VBA Syntax Problem

    What do you want it to do?

  3. #3
    Registered User
    Join Date
    05-17-2012
    Location
    Lexington, KY, USA
    MS-Off Ver
    2010
    Posts
    3

    Re: VBA Syntax Problem

    Here is the entire Macro:

    Sub Convert_to_Values()
    Sheets("Reviews").Select
    Range("Address(Match(0,B2:B198,0),2):Address(Match(0,AF2:AF198,0),32)").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Save
    End Sub

    I am wanting to use it a Workbook that is comprised of Input Form and Datasheet, after the formulas in the Datasheet copy the record being created in the Input Form I need this Macro to convert those formulas to values on that specific row, which is why the Address and Match functions within the Range selection. I have used something similar in years past, but evidently forgotten the finer points in how to write the code.

    Thanks

  4. #4
    Forum Contributor
    Join Date
    04-11-2011
    Location
    Columbus, Ohio
    MS-Off Ver
    Excel 2007
    Posts
    325

    Re: VBA Syntax Problem

    From your code I think I know what you are trying to do but I couldn't get the syntax to work either. The following code should work in place of it though. You just have to adjust the ranges and what you are looking for as needed. Maybe someone with more experience will be able to help you.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-17-2012
    Location
    Lexington, KY, USA
    MS-Off Ver
    2010
    Posts
    3

    Thumbs up Re: VBA Syntax Problem

    111StepsAhead:

    I can't thank you enough, it worked with a couple of very minor adjustments to the code and some changes to my worksheet. You made my week, I will drink a toast in your honor afterwork tonight with a glass of Stout from our great Microbrewery, West 6th Brewing. If you are ever in Lexington, I am buying!

    Gregg Bracke

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1