Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

Excel search Range

Hi - I have the following code that will open an Excel spreadsheet and return the values in Column A.  Let’s say that cell A1 is "boc ctgg-documentum", I want the program to then read a select range (C1:C3) and return the data in each cell.  Here’s the code that I have so far.  Any ideas?  Thanks for the help.

'------------------------------------------------------------------------------------------------------------------
Public Function CheckGroupInformation()
On Error Resume Next
   
    Dim xl As New Excel.Application
    Dim wb As Excel.Workbook
    Dim ws As Excel.Worksheet
   
    Set wb = xl.Workbooks.Open(txtPath.Text)
    Set wb = xl.Workbooks.Open(strGroupDrive)
    Set ws = wb.Worksheets("Group")

    'counts the number of rows within the workbook
    wb.Application.ActiveCell.SpecialCells(xlCellTypeLastCell).Select
    strCells = wb.Application.ActiveCell.Row
       
    'loop that gets name & then creates folders
    For r = 1 To strCells
        x = ws.Cells(r, 1)
               
        strGroupName = x
        Call CheckUserName
   
        For Each x In strCells
            If x = "boc ctgg-documentum" Then
                For Each y In strCells
                    y = ws.Range("C1")
                    Debug.Print y
                Next y
            End If
               
            Call MembersOfGroup(strGroupName, "mycompany.net", strUserName)
             
            If strGroupMembers = "1" Then
                ws.Cells(r, 2).Value = "Is a member"
                ws.Cells(r, 2).Font.Bold = True
                ws.Cells(r, 2).Font.Color = vbBlue
            End If
        Next x
    Next r
   
    'cleanup
    wb.Application.Workbooks(1).Save
    wb.Close
    xl.Quit
   
    cmdMoveFiles.Visible = False
    cmdDone.Visible = True
   
    Set wb = Nothing
    Set xl = Nothing
   
End Function
'------------------------------------------------------------------------------------------------------------------
0
eciabattari
Asked:
eciabattari
1 Solution
 
kay2gCommented:
I think there may be a problem where you start to loop to get names and create folders. I think you are starting too many loops that cycle through nothing. strCells as declared in your program is a Long for all I know (I also dont know the contents of the CheckUserName procedure, so revealing that would help indeed). If strCells is a Long for what I know, it cannot be cycled using the For Each command since it is not a collection of some sort. Take a look at this snippet:

'loop that gets name & then creates folders
    For r = 1 To strCells
        x = ws.Cells(r, 1)
               
        strGroupName = x  
        Call CheckUserName
       
         'I think here lies the problem    
         If x = "boc ctgg-documentum" Then
                y = ws.Range("C1:C3")
                Debug.Print y
         End If
               
         Call MembersOfGroup(strGroupName, "mycompany.net", strUserName)
             
         If strGroupMembers = "1" Then
             ws.Cells(r, 2).Value = "Is a member"
             ws.Cells(r, 2).Font.Bold = True
             ws.Cells(r, 2).Font.Color = vbBlue
         End If

    Next r

I hope this helps

Kay
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now