1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Excel to VBA code

Discussion in 'VBA Macros' started by Krishna2385, Aug 10, 2018.

  1. Krishna2385

    Krishna2385 New Member

    Messages:
    20
    Hi Team,

    I Have an Excel Macro, however when I checked with the VBA macro it is too huge.

    Can the below macro be converted to VBA macro or I need to write a new one

    Code (vb):
    Sub Macro2()
    '
    ' Macro2 Macro
    '

    '
     ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-1],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C2,2,0)"
      Range("F2").Select
      Selection.Copy
      Range("E2").Select
      Selection.End(xlDown).Select
      Range("F137").Select
      Range(Selection, Selection.End(xlUp)).Select
      Range("F3:F137").Select
      Range("F137").Activate
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Range("F136").Select
      Selection.End(xlUp).Select
      Range("G2").Select
      ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-2],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C4,4,0)"
      Range("G2").Select
      Selection.Copy
      Range("F2").Select
      Selection.End(xlDown).Select
      Range("G137").Select
      Range(Selection, Selection.End(xlUp)).Select
      Range("G3:G137").Select
      Range("G137").Activate
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Selection.End(xlUp).Select
      Range("H2").Select
      ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-3],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C5,5,0)"
      Range("H2").Select
      Selection.Copy
      Range("G2").Select
      Selection.End(xlDown).Select
      Range("H137").Select
      Range(Selection, Selection.End(xlUp)).Select
      Range("H3:H137").Select
      Range("H137").Activate
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Selection.End(xlUp).Select
      Range("I2").Select
      ActiveCell.FormulaR1C1 = _
      "=VLOOKUP(RC[-4],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C3,3,0)"
      Range("I2").Select
      Selection.Copy
      Range("H2").Select
      Selection.End(xlDown).Select
      Range("I137").Select
      Range(Selection, Selection.End(xlUp)).Select
      Range("I3:I137").Select
      Range("I137").Activate
      ActiveSheet.Paste
      Application.CutCopyMode = False
      Selection.End(xlUp).Select
      Range("F2:I2").Select
      Range(Selection, Selection.End(xlDown)).Select
      Selection.Copy
      Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
      Range("F1").Select
      Application.CutCopyMode = False
    End Sub
    Please help in getting this sorted out.
    Many thanks
    Last edited by a moderator: Aug 10, 2018
  2. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Hi !

    Please edit your post and use code tags or the specific code icon, thanks !

    As it's not a macro (as macros disappears at least 20 years ago !)
    but already a VBA code so nothing to do …
  3. Krishna2385

    Krishna2385 New Member

    Messages:
    20
    Sorry , how to edit I dint find any option.

    And isn't noting to do with this code... thought this can be shirked.
    thanks
  4. Luke M

    Luke M Excel Ninja

    Messages:
    9,380
    Here's your code, simplified down at least.
    Code (vb):

    Sub CleanedCode()
       
        Application.ScreenUpdating = False
       
        'Dangerous, don't know what active cell will be!
       ActiveCell.FormulaR1C1 = _
            "=VLOOKUP(RC[-1],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C2,2,0)"
        Range("F2").Copy Range("F3:F137")
        Range("G2").FormulaR1C1 = _
            "=VLOOKUP(RC[-2],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C4,4,0)"
        Range("G2").Copy Range("G3:G137")
        Range("H2").FormulaR1C1 = _
            "=VLOOKUP(RC[-3],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C5,5,0)"
        Range("H2").Copy Range("H3:H137")
        Range("I2").FormulaR1C1 = _
            "=VLOOKUP(RC[-4],'[PeopleSoft SPR query.xlsx]Sheet1'!C1:C3,3,0)"
        Range("I2").Copy Range("I3:I137")
        With Range("F2:I137")
            .Copy
            .PasteSpecial Paste:=xlPasteValues
        End With
       
        Application.CutCopyMode = False
        Application.ScreenUpdating = True
    End Sub
  5. Krishna2385

    Krishna2385 New Member

    Messages:
    20
    Thanks a lot... wish I could buy u a drink :)

Share This Page