Searching For a value in a range and write a texte if the value is found

[youbitto]

Hello!

I have multiple Sheets This Ref contains "G"

the first one where I convert to PDF for each reference

Test.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7
8
9
10
11
12
13		Ref :	017118200320272			Comment
14
15
16
17
18
19
20
Branchement

The second sheet is where I take the information from

Test.xlsx
	A
2
3
4	Ref
5	017051500050157
6	017051502408156
7	017051503018136
8	017051503066167
9	017051503080117
10	017051503096100
11	017059651030139
12	017118200320272
13
14
TB

The Third one is where the values to search

Test.xlsx
	A	B
1	Ref	type
2	017051500050157	E
3	017051502250195	E
4	017051502408156	E
5	017051503018136	E
6	017051503066167	E
7	017051503080117	E
8	017051503096100	E
9	017059651030139	E
10	017059651030139	G
11	017118200320272	E
12	017118200320272	G
Contrat

And this is the Code I use to create PDF for each Refrence

Sub Brt()

Dim Ref As Range

Application.ScreenUpdating = False

lastrow = Sheets("TB").Cells(Rows.Count, "A").End(xlUp).Row

    Dim rngSrc As Range
    Dim arrRef As Variant

Set rngSrc = Sheets("TB").Range("A5:A" & lastrow)
        arrRef = rngSrc.Value2

For i = 5 To lastrow

Sheets("Branchement").Range("C12:D12").ClearContents                                'Vider les cellules
Sheets("Branchement").Range("G12:G13").ClearContents

    
    Sheets("TB").Cells(i, 1).Copy                                 'Réference
    Sheets("Branchement").Range("C13").PasteSpecial Paste:=xlPasteValuesAndNumberFormats
    
    Sheets("Branchement").Cells(13, 3).NumberFormat = "@"         'Format texte de cellele C13

    Application.CutCopyMode = False

'________________PDF

Dim wsA As Worksheet
Dim wbA As Workbook
Dim strName As String
Dim strPath As String
Dim strFile As String
Dim strPathFile As String
Dim myFile As Variant

Set wbA = ActiveWorkbook
Set wsA = Sheets("Branchement")
'
strPath = wbA.Path
If strPath = "" Then
  strPath = Application.DefaultFilePath
End If

strPath = strPath & "\"

strName = wsA.Range("C13").Value _

'create default name for savng file
strFile = strName & " Fiche branchement" & ".pdf"
strPathFile = strPath & strFile

   'export to PDF in current folder
    wsA.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:=strPathFile, _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
      
    Next i

Application.ScreenUpdating = True



End Sub

My Request is that some refrences are doubled and contains "E" and "G" as you can see in "Contrat" sheet
I want while creating a PDF that if any refrence that contains "G" the value of the cell G13 in sheet "PDF" will be "This refrence Contains G"

 

[DanteAmor]

I simplified your code a bit and added the part to check if it has "G":

Sub Brt()
  Dim wsA As Worksheet, wsC As Worksheet
  Dim strPath As String, strFile As String
  Dim c As Range
  
  Application.ScreenUpdating = False
  Set wsA = Sheets("Branchement")
  Set wsC = Sheets("Contrat")
  strPath = ActiveWorkbook.Path
  If strPath = "" Then strPath = Application.DefaultFilePath
  
  For Each c In Sheets("TB").Range("A5", Sheets("TB").Range("A" & Rows.Count).End(xlUp))
    wsA.Range("C12:D12, G12:G13").ClearContents                     'Vider les cellules
    wsA.Range("C13").Value = c.Value
    
    'Check if the reference contains G
    If WorksheetFunction.CountIfs(wsC.Range("A:A"), c.Value, wsC.Range("B:B"), "G") > 0 Then _
       wsA.Range("G13").Value = "This refrence Contains G"
    
    'Create default name for savng file and export to PDF in current folder
    strFile = c.Value & " Fiche branchement" & ".pdf"
    wsA.ExportAsFixedFormat xlTypePDF, strPath & "\" & strFile, xlQualityStandard, True, False, OpenAfterPublish:=False
  Next
  Application.ScreenUpdating = True
End Sub

 

[youbitto]

I simplified your code a bit and added the part to check if it has "G":

Sub Brt()
  Dim wsA As Worksheet, wsC As Worksheet
  Dim strPath As String, strFile As String
  Dim c As Range
 
  Application.ScreenUpdating = False
  Set wsA = Sheets("Branchement")
  Set wsC = Sheets("Contrat")
  strPath = ActiveWorkbook.Path
  If strPath = "" Then strPath = Application.DefaultFilePath
 
  For Each c In Sheets("TB").Range("A5", Sheets("TB").Range("A" & Rows.Count).End(xlUp))
    wsA.Range("C12:D12, G12:G13").ClearContents                     'Vider les cellules
    wsA.Range("C13").Value = c.Value
   
    'Check if the reference contains G
    If WorksheetFunction.CountIfs(wsC.Range("A:A"), c.Value, wsC.Range("B:B"), "G") > 0 Then _
       wsA.Range("G13").Value = "This refrence Contains G"
   
    'Create default name for savng file and export to PDF in current folder
    strFile = c.Value & " Fiche branchement" & ".pdf"
    wsA.ExportAsFixedFormat xlTypePDF, strPath & "\" & strFile, xlQualityStandard, True, False, OpenAfterPublish:=False
  Next
  Application.ScreenUpdating = True
End Sub

Thank you very Much Mr. Dante it works perfectly

​