CODEWITHSUNDEEP
vbaexcelrange
noitib
noitib

Reputation: 159

Using a value from one worksheet, check for values on another worksheet and return to a cell

I have two workbooks. Let's call them "Main" and "Aux".

I'm writing a function that will run on "Main" and will use data from "Aux".

"Main" has a list of people on one worksheet. For each person, I want to check on workbook "Aux" which skills that person has with grade >=3, to concatenate them and fill a cell on workbook "Main".

This is the code I've wrote so far. The problem is the range is referring to the "Main" workbook, and the data I want is on "Aux" workbook.

A skill is identified when Column "A" has "Technical"

Code:

Public Function Arroz(name As String) As String

    Dim rngFound As Range
    Dim strFirst As String
    Dim strID As String
    Dim strDay As String
    Dim skillsAs String
    Dim deb As String
    Dim deb2 As String


    strID = "Person number one"
    strDay = "Technical"
    skills= ""


    Set rngFound = Workbooks("Aux.xlsx").Sheets("Skills Mao").Columns("G").Find(strID, Cells(Rows.Count, "G"), xlValues, xlWhole)

    If Not rngFound Is Nothing Then
        strFirst = rngFound.Address
        Do
            deb = Cells(rngFound.Row, "A").Text
            deb2 = Cells(rngFound.Row, "M").Value
            MsgBox deb
            MsgBox deb2
            If LCase(Cells(rngFound.Row, "A").Text) = LCase(strDay) And Cells(rngFound.Row, "M").Value >= 4 Then
               skills = skills & Cells(rngFound.Row, "D").Text & " ,"
            End If
            Set rngFound = Columns("G").Find(strID, rngFound, xlValues, xlWhole)
        Loop While rngFound.Address <> strFirst
    End If
    strLast = rngFound.Address
    Set rngFound = Nothing

    Arroz = competencies
    Exit Function
    End Function

Can someone help me figure out what I'm doing wrong?

Upvotes: 1

Views: 44

Answers (1)

CodeJockey
CodeJockey

Reputation: 1981

Your problem is the use of Cells without specifying a sheet. When you do that Cells will refer to the active sheet i.e. "Main" as you have named it. Try something like this:

' ------ More of your existing code above ------
strID = "Person number one"
strDay = "Technical"
skills= ""

' ----- My added snippit -----
Dim shAux as Worksheet
Set shAux = Workbooks("Aux.xlsx").Sheets("Skills Mao")
Set rngFound = shAux.Columns("G").Find(strID, shAux.Cells(Rows.Count, "G"), xlValues, xlWhole)

Then use shAux.Cells wherever you want cells from that sheet in place of Cells alone.

Upvotes: 1

Related Questions