Consulting

Results 1 to 2 of 2

Thread: Dynamic Function

  1. #1
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location

    Dynamic Function

    Hello! I want to create a dynamic function in VBA wherein I provide the name range, column number and row number. As per the values provided, the range should be hard pasted. This is what I did but it is giving an error:


    Public Function HardPaste(Table_Range As Variant, Col_Num As Integer, Row_Num As Integer)
    Table_Range(Row_Num, Col_Num).Select
    Selection.PasteSpecial Paste:=xlPasteValues
    End Function
    This is how I am using it:

    Sub Test()
    ThisWorkbook.Names.Add Name:="CQ_Table_Range", RefersTo:=ThisWorkbook.Sheets("2019_QF3").Range("CQ_Table_Start").CurrentRegion
    HardPaste(CQ_Table_Range,1,4)
    End Sub


    The error I am getting is Compile Error: Expected :=

    Thanks in advance for your help!

  2. #2
    VBAX Regular
    Joined
    Dec 2019
    Posts
    9
    Location
    I tried another this and it worked:

    Public Function HardPaste(Table_Range As Range, Col_Num As Integer, Row_Num As Integer)
    Range(Table_Range(Row_Num, Col_Num), Table_Range(Table_Range.Rows.Count, Table_Range.Columns.Count)).Copy
    Table_Range(Row_Num, Col_Num).PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
    End Function
    Thanks!

Posting Permissions

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