VBA - Unable to manage 3rd loop where MsgBox works

[Sayth]

Hi

What I am trying to do is match a team name from a cell in range "B8:J8" with a team name on a separate page in the range "C2:C337".

From those matches I want to determine the highest score and return that in a cell in range B20:J20

So for each team name in B8:J8 return the highest score for that team in the corresponding cell in B20:J20.

I can acheive this with a msgBox prompt however cannot with a 3rd loop to supply the answers from the highestvalue found.

Option Explicit


Sub sechigh()


Dim rngcd As Range, rnglk As Range, rngans As Range, cell1 As Range, cell2 As Range, cell3 As Range
Dim wb As Workbook
Dim wscd As Worksheet, wslk As Worksheet
Dim highestValue As Double, secondHighestValue As Double, cellval As Double


highestValue = 0
secondHighestValue = 0
cellval = 0


Set wscd = Sheets("Central Draft")
Set wslk = Sheets("Lookup")
Set rngcd = wscd.Range("B8:J8")
Set rnglk = wslk.Range("C2:C337")
Set rngans = wscd.Range("B20:J20")

For Each cell1 In rngcd
For Each cell2 In rnglk

        If StrComp(cell1, cell2, vbBinaryCompare) = 0 Then
            cellval = cell2.Offset(0, 2).Value
        End If
        If cellval < 1 And cellval > highestValue Then highestValue = cellval
                
Next cell2
MsgBox highestValue
Next cell1
End Sub

 

[DanteAmor]

You can use the following:

for each team name in B8:J8, return the highest score for that team in the corresponding cell in B20:J20

Sub Macro3()
    With Sheets("Central Draft").Range("[COLOR=#0000ff]B20:J20[/COLOR]")
        .Formula = "=SUMPRODUCT(MAX((Lookup!$C$2:$C$337='Central Draft'![COLOR=#008000]B8[/COLOR])*(Lookup!$E$2:$E$337)))"
        .Value = .Value
    End With
End Sub

---

If you want it with loop, I recommend using .Find to search faster.

Sub macro4()
    Dim wscd As Worksheet, wslk As Worksheet
    Dim r As Range, f As Range, cell As String
    Dim highestValue As Double, cell1 As Range
    
    Set wscd = Sheets("Central Draft")
    Set wslk = Sheets("Lookup")
    
    For Each cell1 In wscd.Range("B8:J8")
        Set r = wslk.Range("C2:C337")
        Set f = r.Find(cell1.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            cell = f.Address
            highestValue = 0 'f.Offset(0, 2)
            Do
                If f.Offset(0, 2) > highestValue Then highestValue = f.Offset(0, 2)
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
            cell1.Offset(12).Value = highestValue
        End If
    Next cell1
End Sub

 

[Sayth]

You can use the following:

Sub Macro3()
    With Sheets("Central Draft").Range("[COLOR=#0000ff]B20:J20[/COLOR]")
        .Formula = "=SUMPRODUCT(MAX((Lookup!$C$2:$C$337='Central Draft'![COLOR=#008000]B8[/COLOR])*(Lookup!$E$2:$E$337)))"
        .Value = .Value
    End With
End Sub

I am wondering in this one how we are looping through B8:J8?

 

[DanteAmor]

I am wondering in this one how we are looping through B8:J8?

Actually there is no loop.
What it does is put the formula in B20:J20 and for each cell it reads its respective value from B8 to J8

 

[Sayth]

Trying to modify the loop version to create a combined output of name and result.

Sub macro4()
    Dim wscd As Worksheet, wslk As Worksheet
    Dim r As Range, f As Range, cell As String, consultant As String, output As String
    Dim highestValue As Double, cell1 As Range
    
    Set wscd = Sheets("Central Draft")
    Set wslk = Sheets("Lookup")
    
    For Each cell1 In wscd.Range("B8:J8")
        Set r = wslk.Range("C2:C337")
        Set f = r.Find(cell1.Value, LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            cell = f.Address
            highestValue = 0 'f.Offset(0, 2)
            Do
                If f.Offset(0, 2) > highestValue And f.Offset(0, 2) < 1 Then consultant = f.Offset(0, 1) And highestValue = f.Offset(0, 2)
                'Set consultant = f.Offset(0, 1)
                Set f = r.FindNext(f)
            Loop While Not f Is Nothing And f.Address <> cell
            cell1.Offset(12).Value = f.Formula = "consultant&" "&highestValue"
            'cell1.Offset(12).Value = highestValue
        End If
    Next cell1
End Sub

What seems odd is that I created a variable "consultant" and set it to offset(0,1) when offset(0,2) is the highest value. Offset(0,1) is a persons name. It returns a type mismatch between string and Offset(0,1) which is also a string from a cell with format General.

I noticed last time in non loop version you used .Formula to use an excel formula in VBA. I am assuming that you need a with statement to use the .Formula syntax.
My attempt at using f to create an output syntax has error.

cell1.Offset(12).Value = f.Formula = "consultant&" "&highestValue"

 

[DanteAmor]

I do not understand what do you need.
The 2 solutions I gave you worked for your original request?

 

[Sayth]

I do not understand what do you need.
The 2 solutions I gave you worked for your original request?

I agree, I liked your code and wanted to take it further.

So i have been tinkering. I thought in your loop code if I took the offset 1 to the left of the highestValue code you gave I could get the name of the person and the value.

However, when I create a variable "consultant" and then set it to f.Offset(0,1) which is a persons name I get a type mismatch. However, both are strings.

If Not f Is Nothing Then
cell = f.Address
highestValue = 0 'f.Offset(0, 2)
Do
If f.Offset(0, 2) > highestValue And f.Offset(0, 2) < 1 Then consultant = f.Offset(0, 1) And highestValue = f.Offset(0, 2)
'Set consultant = f.Offset(0, 1)
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
cell1.Offset(12).Value = f.Formula = "consultant&" "&highestValue"
'cell1.Offset(12).Value = highestValue
End If

 

[DanteAmor]

I agree, I liked your code and wanted to take it further.

So i have been tinkering. I thought in your loop code if I took the offset 1 to the left of the highestValue code you gave I could get the name of the person and the value.

However, when I create a variable "consultant" and then set it to f.Offset(0,1) which is a persons name I get a type mismatch. However, both are strings.

If Not f Is Nothing Then
cell = f.Address
highestValue = 0 'f.Offset(0, 2)
Do
If f.Offset(0, 2) > highestValue And f.Offset(0, 2) < 1 Then consultant = f.Offset(0, 1) And highestValue = f.Offset(0, 2)
'Set consultant = f.Offset(0, 1)
Set f = r.FindNext(f)
Loop While Not f Is Nothing And f.Address <> cell
cell1.Offset(12).Value = f.Formula = "consultant&" "&highestValue"
'cell1.Offset(12).Value = highestValue
End If

I still do not understand what you need.
But change the line for this:

cell1.Offset(12).Value = consultant &" "& highestValue