• 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...

  • 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

Krishna2385

New Member
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:
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:

Marc L

Excel Ninja
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 …
 

Krishna2385

New Member
Sorry , how to edit I dint find any option.

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

Luke M

Excel Ninja
Here's your code, simplified down at least.
Code:
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
 
Top