locked
Function for finding string and returning copied data in range RRS feed

  • Question

  • I know this is an old thread and I appreciate any help that I can get. I am attempting to write a function that I can call that does the following.

    1. Accepts a column name.
    2. Accept a worsheet name where the column name is located.
    3. Look in the worksheet and located the cell with the Column name
    4. After finding the column name, copy the range of data below it.
    5. Return the copied data.

    After I return this data, how can I reference it to paste it into another worksheet? 

    Thanks. Also, I found the above post very useful for the copy paste. :)

    Wednesday, February 22, 2012 9:19 PM

All replies

  • It might help if you post the thread you are referring to. Thanks! :)


    Daniel van den Berg | Washington, USA | "Anticipate the difficult by managing the easy"

    Wednesday, February 22, 2012 11:47 PM
  • Sub GetInputAndCopyData()
    Dim strSheetName As String
    Dim strColName As String
    Dim rngInp As Range
    
    strSheetName = InputBox("Enter a Sheet Name ")
    strColName = InputBox("Enter a Column Name ")
    
    If IsValidColumn(strSheetName, strColName) Then
      Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strColName & "1")
      Set rngInp = Range(rngInp, rngInp.End(xlDown))
    ' copy the column
      rngInp.Copy Destination:=Selection
    End If
    
    End Sub
    
    ' check that column is valid
    ' this first checks that the sheet name is valid, then checks that column
    ' has data in rows 1 and 2
    Function IsValidColumn(ByVal wks As String, ByVal col As String) As Boolean
    Dim r As Range
    On Error GoTo NotValid
    If IsValidSheet(wks) Then
    ' error if column does not exist
      Set r = ThisWorkbook.Worksheets(wks).Range(col & "1")
    ' check that column has data, must have data in at least first two rows
      If r.Value = "" And r.Offset(1, 0).Value = "" Then
        IsValidColumn = False
      Else
        IsValidColumn = True
      End If
    End If
      Exit Function
    NotValid:
      IsValidColumn = False
    End Function
    
    Function IsValidSheet(ByVal str As String) As Boolean
    Dim wks As Worksheet
    On Error GoTo NotValid
    ' error if sheet does not exist
      Set wks = ThisWorkbook.Worksheets(str)
      IsValidSheet = True
      Exit Function
    NotValid:
      IsValidSheet = False
    End Function


    Ed Ferrero
    www.edferrero.com

    Thursday, February 23, 2012 12:13 AM
  • Ed, Thanks for the response. This looks pretty good, but I'm not interested in an Input box, I want to be able to pass the SheetName and ColName to the GetInputAndCopyData function via a call and also, What is the check for data in Rows 1 and 2 for in the IsValidColumn?

    Rlg4

    Thursday, February 23, 2012 3:55 PM
  • Ed, Here's what I have for passing the variables, I am new to vba. :) If this looks correct, I know the row will always be row 7, so how can I get it to check this row via a variable (i.e., rngInp = 7).

    Sub GetInputAndCopyData(strSheetName As String, strColName As String)
        Dim strSheetName As String
        Dim strColName As String
        Dim rngInp As Range
        
        'strSheetName = InputBox("Enter a Sheet Name ")
        'strColName = InputBox("Enter a Column Name ")
        
        If IsValidColumn(strSheetName, strColName) Then
            Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strColName & "1")
            Set rngInp = Range(rngInp, rngInp.End(xlDown))
            'copy the column
            rngInp.copy Destination:=Selection
        End If
    
    End Sub


    Rlg4

    Thursday, February 23, 2012 3:58 PM
  • Hi Rlg4,

    Yes, your changes to the code look ok.

    The reason I check for data in the first two rows of the input range is that I am using .End(xlDown) to grab data from the source column. If the first couple of cells are blank, we will end up trying to copy a whole column of blank cells, I assume you do not want that. But mostly I was demonstrating how to check for bad inputs i.e. pass a worksheet name that does not exist, pass a column that does not exist (you could specify column AAA as the source column in Excel 2010, but this would cause an error in Excel 2000).

    If you know that the first row of the source range is always 7, you could change the code to

    Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strColName & "7")

    Or, you could just pass the first cell of the range, say C7 instead of column C. Then the code would need to be modified to;

    Sub GetInputAndCopyData(strSheetName As String, strCellName As String)
        Dim rngInp As Range
        
        If IsValidCell(strSheetName, strCellName) Then
            Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strCellName)
            Set rngInp = Range(rngInp, rngInp.End(xlDown))
            rngInp.Copy Destination:=Selection
        End If
    End Sub
    ' check that cell is valid
    ' this first checks that the sheet name is valid, then checks that cell
    ' has data in rows 1 and 2
    Function IsValidCell(ByVal wks As String, ByVal cell As String) As Boolean
    Dim r As Range
    On Error GoTo NotValid
    If IsValidSheet(wks) Then
    ' error if column does not exist
      Set r = ThisWorkbook.Worksheets(wks).Range(cell)
    ' check that column has data, must have data in at least first two rows
      If r.Value = "" And r.Offset(1, 0).Value = "" Then
        IsValidColumn = False
      Else
        IsValidColumn = True
      End If
    End If
      Exit Function
    NotValid:
      IsValidColumn = False
    End Function
    Function IsValidSheet(ByVal str As String) As Boolean
    Dim wks As Worksheet
    On Error GoTo NotValid
    ' error if sheet does not exist
      Set wks = ThisWorkbook.Worksheets(str)
      IsValidSheet = True
      Exit Function
    NotValid:
      IsValidSheet = False
    End Function

    Note that I have modified the validity checks here.

    Also, If you are going to call this from code, and you have the source range, why not just pass the range you wish to copy? Then you only need to set the source range in your main code and add one line to your original code

    rngInp.Copy Destination:=Selection


    Ed Ferrero
    www.edferrero.com

    Thursday, February 23, 2012 11:40 PM
  • Makes sense. Now I'm having an issue calling this function from my main code (Note: my main code is another module, if that matters. Here's how I'm calling the function:

    GetInputAndCopyData(Analog(2), EMS Composite Name


    Rlg4

    Monday, February 27, 2012 1:55 PM
  • Hi Ed. Here's what I have. and I'm getting an error when I run it.
    Sub Analog()
            
            GetInputAndCopyData (Analog (2), Device Name)
            
            
            
            
            'Call subroutine (FinalFormatting) to perform Final Formatting
            'Finalize
            
            'Call subroutine (Print Formatting) to format the worksheets for printing
            'PFormat
            
    End Sub
    
    Sub GetInputAndCopyData(strSheetName As String, strColName As String)
        Dim rngInp As Range
        
        If IsValidCell(strSheetName, strColName) Then
            Set rngInp = ActiveWorkbook.Worksheets(strSheetName).Range(strColName & "6")
            Set rngInp = Range(rngInp, rngInp.End(xlDown))
            rngInp.copy Destination:=Selection
        End If
    End Sub
    ' check that cell is valid
    ' this first checks that the sheet name is valid, then checks that cell
    ' has data in rows 1 and 2
    Function IsValidCell(ByVal wks As String, ByVal cell As String) As Boolean
    Dim r As Range
    On Error GoTo NotValid
    If IsValidSheet(wks) Then
    ' error if column does not exist
      Set r = ThisWorkbook.Worksheets(wks).Range(cell)
    ' check that column has data, must have data in at least first two rows
      If r.Value = "" And r.Offset(1, 0).Value = "" Then
        IsValidColumn = False
      Else
        IsValidColumn = True
      End If
    End If
      Exit Function
    NotValid:
      IsValidColumn = False
    End Function
    Function IsValidSheet(ByVal str As String) As Boolean
    Dim wks As Worksheet
    On Error GoTo NotValid
    ' error if sheet does not exist
      Set wks = ThisWorkbook.Worksheets(str)
      IsValidSheet = True
      Exit Function
    NotValid:
      IsValidSheet = False
    End Function
    
    
    
    


    Rlg4

    Wednesday, February 29, 2012 3:12 PM
  • Ok, I have the function returning data (I think), but why did I have to put a Call before the function call? I have not had to do that prior to this instance. Also, the code doesn't appear to actually be copying the data from strSheetName. Any suggestions?

    Rlg4

    Friday, March 2, 2012 3:38 PM
  • Btw, This is the only function that I have that passes arguments. Is that why I need a call before it? Also, Here's a code snipet of what I have. The function I'm calling is a previous reply.

    Call GetInputAndCopyData("Analog (2)", "EMS Composite Name")
            Sheets("Analog").Select
            ActiveSheet.Paste
            Application.CutCopyMode = False

    When I run this, the code pastes the actual code below the Call GetInputAndCopyData function in the Analog worksheet. Why is this happening? It should be pasting the data copied during the run of GetInputAndCopyData.


    Rlg4

    Friday, March 2, 2012 4:04 PM