Build a string

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
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:

Code:
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!
 
Last edited:

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,345
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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:

Code:
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

Board Regular
Joined
Oct 7, 2014
Messages
157
Thanks!
In the moment this all code.
celP and rngProt are variables type Range.
Thanks for the help.
 

Leandroarb

Board Regular
Joined
Oct 7, 2014
Messages
157
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:

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

Board Regular
Joined
Oct 7, 2014
Messages
157
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!

Code:
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
 
Last edited:

Forum statistics

Threads
1,141,204
Messages
5,704,954
Members
421,372
Latest member
Jamie11

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top