Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Giro · Nov 18th 2016

Hi

Trying to create a process either using formula or VBA to reduce the time spent searching for a specific agents name and copy a specific data highlighted in red (image).

Used index match match (unable to obtain the specific cell)

Used vlookup (unable to obtain the specific cell)

The problem i am having is the data I received depending on the attendance of each agent, the location of the data can be in different every time, also the rows and columns is not like a standard row with the header stating Agent.

Tried recording (relative or absolute) Marco to see if i can search by name, and from that start point move down 6 columns and move 6 cells to the right.

Code

Sheets("worksheet1").Select
    Cells.Find(What:="Agent 16914", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(7, 11).Range("A1:A2").Select
    Selection.Copy
    Sheets("Adherence").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

The above code didn't work either.

Here is an example how the data is pulled as you can see the rows and column is not align to make it easy to search.
[ATTACH=CONFIG]70681[/ATTACH]

Any other method that you guys/girls can suggest?

Thank you

**alansidman** · Nov 18th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Picture is to small to analyze and difficult to understand. Suggest you upload an actual sample file.

Giro · Nov 19th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Cannot seems to upload an excel file to this forum. Sent a note to admin for permission to add file to this forum.

**alansidman** · Nov 19th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

click on the Go Advanced Button in the reply thread and then scroll down to the Manage Attachments and follow the wizard.

Giro · Nov 21st 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Here is the test sample in excel forum.ozgrid.com/index.php?attachment/70695/

Giro · Nov 21st 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

added the sample file

Giro · Nov 29th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Hi AlanSidman,

Just wondering if you got the chance to look at the attachment if there is any possible way to locate the cell highlighted in red using VBA?
Quick reminder, that the agent's location will be in different place each week depending on attendance

Thank you very much in advance for looking into this.

**alansidman** · Nov 29th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Have been on vacation for past week. Here is a VBA solution that looks for cells with a red background and returns a message.

Code

Option Explicit




Sub RedCells()
    Dim lr As Long
    lr = Range("V" & Rows.Count).End(xlUp).Row
    Dim c As Range
    Dim rng As Range
    Set rng = Range("C17:V" & lr)
    For Each c In rng
        If c.Interior.Color = RGB(255, 0, 0) Then
            MsgBox (c.Address)
        End If
    Next c




End Sub

Display More

Giro · Nov 30th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Hi AlanSidman,

The VBA looks like it is searching by the color code: I purposely highlighted in red to show you that is the number i wanted from the data. Apologize for the confusion.

what i am trying to achieve here
Searching by agent id (example 8113499) and pull the number below Percent in Adherence
For agent 8113499's percent in adherence should paste into another work sheet beside 8113499 = 21.48%.

I have agents
8113499, 16914, 905, 818000, 818001
I am looking for their percent in adherence in other work sheet.

Keep in mind that the agents position can change depending on their attendance, so the order of agent isn't the same all the time.

Thank you

**alansidman** · Nov 30th 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

My apologies, but because your data is not standardized, I cannot determine a valid solution that will work with your scenario. If you normalize your data then maybe I can offer up a solution, but until then, I do not see any automated way to help.

darkorder · Dec 3rd 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Merged cells and uneven row entries will cause any code to have a lot of problems finding the data you want it to find (as AlanSidman intimated). Although you can add a number of If/Then checks, to avoid most of the problems in your example, this code will likely need to be adjusted each time something changes.

Code

Sub Testing()


Dim LastRow As Long
Dim CopyRow As Long
Dim PInAdherence As String
Dim vTotals As String


CopyRow = 2


LastRow = Sheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Row


If LastRow > 2 Then
Sheets("Sheet1").Range("B2:C" & LastRow & "").ClearContents
End If


LastRow = Sheets("CRG ").Cells(Rows.Count, "B").End(xlUp).Row


For StartRow = 12 To LastRow
        
     If ActiveSheet.Name = "CRG " Then
     Sheets("CRG ").Range("C" & StartRow & "").Select
     End If
      
     If Sheets("CRG ").Range("B" & StartRow & "") <> "" Then
     vTotalCell = Sheets("CRG ").Range("B" & StartRow & "")
     Else
     vTotalCell = Sheets("CRG ").Range("C" & StartRow & "")
     End If
     
        If InStr(vTotalCell, "Total for ") > 0 Then
            
            PInAdherence = Sheets("CRG ").Range("N" & StartRow - 4 & "")
            
            If PInAdherence = "" Then
            PInAdherence = Sheets("CRG ").Range("N" & StartRow - 5 & "")
            End If
              
              Sheets("Sheet1").Range("B" & CopyRow & "") = Replace(vTotalCell, "Total for ", "")
             
              If PInAdherence = "" Then
              Sheets("Sheet1").Range("C" & CopyRow & "") = "**NOT FOUND**"
              Else
              Sheets("Sheet1").Range("C" & CopyRow & "") = PInAdherence
              END IF


              CopyRow = CopyRow + 1
              
        End If
 
Next


End Sub

Display More

Smallman · Dec 3rd 2016

Re: Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Hi

Follow these instructions perfectly and you will find the promised land.

In Cell Z24 put this formula

=MID(C24,SEARCH("for",C24,1)+4,10)*1

In AA24 put this formula.

=IF(Z24,N31,"")

You are done. Please don't make me post the workbook to show it humming.

Take care

Smallman

Search specific name and copy a cell from an non-adjacent/unorganized worksheet

Participate now!

Share