Build a string

[Leandroarb]

Hi
I have a range in which the same code is repeated, but this code has another code called Priority. I need to go through the codes and build a string with the Priorities.

Exampe:


Code Priority String
10.......1........12
10 ......2........12


I try with this For Each:

For Each celP In rngProt
        If celP = Protocolo Then
                str = str & Prioridade
       End If
Next

But the result is:

Code Priority String
10.......1.........11
10.......2.........22

Thanks in advanced!

 

[Rick Rothstein]

Hi
I have a range in which the same code is repeated, but this code has another code called Priority. I need to go through the codes and build a string with the Priorities.

Exampe:


Code Priority String
10.......1........12
10 ......2........12


I try with this For Each:

For Each celP In rngProt
        If celP = Protocolo Then
                str = str & Prioridade
       End If
Next

But the result is:

Code Priority String
10.......1.........11
10.......2.........22

Thanks in advanced!

I think it might be useful for you to show us all of your code so we can see how you declare your variables (assuming you do so specifically) and what is assigned to each of them leading up to the code snippet you did post.

 

[Leandroarb]

Thanks!
In the moment this all code.
celP and rngProt are variables type Range.
Thanks for the help.

 

[Leandroarb]

I think it might be useful for you to show us all of your code so we can see how you declare your variables (assuming you do so specifically) and what is assigned to each of them leading up to the code snippet you did post.

Good morning!
This is the all code:

Public Function TIPOPROTOCOLO(IntervaloProt As Range, Protocolo As String, Prioridade As Integer, Qtde As Integer, Cod As Long) As String
On Error Resume Next
Dim rngProt As Range
Dim celP As Range
Dim str
Dim strPrio As String


Set rngProt = IntervaloProt
cont = 0


If Qtde = 1 Then
    TIPOPROTOCOLO = "Único"
    Else
        For Each celP In rngProt
            If celP = Protocolo Then
                str = Prioridade
            End If
        Next
        TIPOPROTOCOLO = str
End If
End Function

 

[Leandroarb]

Hi,
I've come up with a solution.
I know it is not more elegant, but it is. If anyone has a more functional and logical idea, please share.
Thanks!

Global iCol1         As Integer
Global iCol2         As Integer
Global iLin1         As Integer
Global iLin2         As Integer
Global strPrioridades As String

Public Function TIPOPROTOCOLO(IntervaloProt As Range, Protocolo As String, Prioridade As Range, Qtde As Integer, Cod As Long)
On Error Resume Next
Dim rngProt As Range
Dim celP As Range
Dim cont As Integer
Set rngProt = IntervaloProt
cont = 0
iCol1 = 0
iCol2 = 0
iLin1 = 0
iLin2 = 0
If Qtde = 1 Then
    TIPOPROTOCOLO = "Único"
    Else
        For Each celP In rngProt
            If Protocolo = celP Then
                cont = cont + 1
                If cont = 1 Then
                    iCol1 = Prioridade.Column
                    iLin1 = celP.Row
                    Else
                        If cont = Qtde Then
                            iCol2 = Prioridade.Column
                            iLin2 = celP.Row
                        End If
                End If
            End If
        Next
End If
TIPOPROTOCOLO = Left(sPrio, Qtde)
End Function


Public Function sPrio() As String
Dim rngS As Range
Dim celS As Range


Set rngS = Range(Cells(iLin1, iCol1), Cells(iLin2, iCol2))
sPrio = ""
strPrioridades = ""


For Each celS In rngS
    strPrioridades = strPrioridades & celS
Next celS


sPrio = strPrioridades


End Function