CODEWITHSUNDEEP
vbaexcel
sbagnato
sbagnato

Reputation: 496

Excel VBA - Find values in a range and store adjacent cell in variable

I have code that is now able to take a range of values and find the highest, next highest, and so on values and paste them as variables to a different workbook.

What I am now trying to do is find the string (in this case, name) adjacent to a specific value, and paste that string in the other workbook.

I know what I need to use to do that, but I am not getting it right.

The section between the comment lines is where the issue is, as everything else works on its own.

Dim sipRng As Range: Set sipRng = [G1]
Dim rng As Range: Set rng = [C4:C16]
Dim firstVal As Double, secondVal As Double, thirdVal As Double, fourthVal As Double, cel As Range
Dim var As Variant, iSheet As Integer, iRow As Long, iRowL As Long

For Each cel In sipRng
    If InStr(cel.Value, "Inbound AHT") > 0 Or InStr(cel.Value, "Valid Quality Count") > 0 Or InStr(cel.Value, "Valid Quality %") > 0 Then
        firstVal = Application.Small(rng, 1)
        secondVal = Application.Small(rng, 2)
        thirdVal = Application.Small(rng, 3)
        fourthVal = Application.Small(rng, 4)
    ElseIf InStr(cel.Value, "Outbound Calls Made") > 0 Or InStr(cel.Value, "FCR %") > 0 Or InStr(cel.Value, "FLR %") > 0 Or InStr(cel.Value, "Corrected CSAT Average") > 0 Then
        firstVal = Application.Large(rng, 1)
        secondVal = Application.Large(rng, 2)
        thirdVal = Application.Large(rng, 3)
        fourthVal = Application.Large(rng, 4)
    End If
Next cel

'''''''''''''''''
iRowL = Cells(Rows.Count, 2).End(xlUp).Row
For iRow = 1 To iRowL
    If Not IsEmpty(Cells(iRow, 1)) Then
        For iSheet = ActiveSheet.Index + 1 To Worksheets.Count
            var = Application.Match(Cells(iRow, 2).Value, Worksheets(iSheet).Columns(2), 0)
        Debug.Print var
        Next iSheet
    End If
Next iRow
''''''''''''''

nngr2.Activate
Range("B4") = firstVal
Range("B11") = secondVal
Range("B9") = thirdVal
Range("B6") = fourthVal

This is what I start with

enter image description here

This is the goal, only instead of the values, I want the name (in this case, letter)

enter image description here

Upvotes: 0

Views: 1747

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

Here is a starting point, this loads a 2 dimensional array with the values and pastes those values in E4 of the active sheet:

Sub Macro1()

Dim rng As Range
Dim arr(1 To 4, 1 To 2)

Set rng = [C4:C16]

For i = 1 To 4
    arr(i, 2) = Application.WorksheetFunction.Large(rng, i)
    arr(i, 1) = rng.Cells(Application.WorksheetFunction.Match(arr(i, 2), rng, 0)).Offset(, -1).Value
Next i

nngr2.Range("B4").value = arr(1,1)
nngr2.Range("B11").value = arr(2,1)
nngr2.Range("B9").value = arr(3,1)
nngr2.Range("B6").value = arr(4,1)

End Sub

enter image description here

Upvotes: 1

Related Questions