We help IT Professionals succeed at work.

Passing column name to function

Shanan212
Shanan212 asked
on
Medium Priority
290 Views
Last Modified: 2012-05-12
Sub Macro1(idata As String, iOrig As String)
    Dim iRow As Integer

    iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
    Columns("F:F").Select
    
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("F2").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))),"""",DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
   
    Selection.AutoFill Destination:=Range("F2:F" & iRow)
    Range("F2:F" & iRow).Select
    Selection.NumberFormat = "m/dd/yy;@"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=RC[-1]"
    Columns("F:F").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("E:E").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlToLeft

End Sub

Open in new window


Hi,

I have the above function but want it to change according to different columns that I would like to send in.

I do  not know how to change the column names accordinly. Say I want to pass

Macro1 ("J", "K")

then I want all the "F" to be changed to "J" and alll the "E" to be changed to "K"

How do I do this change within the function? (specifically below)

    Columns("F:F").Select
    Range("F2:F" & iRow).Select
    Range("F1").Select

Thanks for the help!
Comment
Watch Question

I think you can use e.g.

Columns(idata & ":" & idata).Select
Mechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013
Commented:
You might try rewriting your macro like this. It will run faster and without screen flicker with screen updating turned off and if you avoid selecting any cells.

Sub Macro1(idata As String, iOrig As String)
Dim iRow As Long
Application.ScreenUpdating = False
iRow = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
      
Columns(idata).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
With Columns(idata)
     .Cells(2).FormulaR1C1 = _
         "=IF(ISERROR(DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))),"""",DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2)))"
    
     .Cells(2).AutoFill Destination:=Range(.Cells(2), .Cells(iRow))
     Range(.Cells(2), .Cells(iRow)).NumberFormat = "m/dd/yy;@"
     .Cells(1).FormulaR1C1 = "=RC[-1]"
     .Copy
     .PasteSpecial Paste:=xlPasteValues
End With
With Columns(iOrig)
     Application.CutCopyMode = False
     .Delete Shift:=xlToLeft
End With
End Sub

Open in new window


byundtMechanical Engineer
CERTIFIED EXPERT
Most Valuable Expert 2013
Top Expert 2013

Commented:
You would call the macro (from another macro) like this:
Macro1 "F", "E"
Glenn RayExcel VBA Developer
CERTIFIED EXPERT
Top Expert 2014

Commented:
I was about to propose a very similar solution to byundt, but his is cleaner.

I will add that you must call this subroutine like so:
Sub SwapEm()
    Macro1 "J", "K"
End Sub

Open in new window

Author

Commented:
Thanks for all your efforts!

I am calling the macro as

Call Macro1 ("A","B")

and it worked!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.