VBA for checking if value exists on another sheet

[kottn]

Hi,

I'm new to VBA and want to do a macro that checks if value in C2 on sheet2 exists in column B at sheet1
If it exists i want a text added to that row on sheet1 at column L

If it does not exist i want an error message on sheet2 E3

Any advice?

 

[mehidy1437]

Try this

Sub CheckValues()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim valueToFind As Variant
    Dim searchRange As Range
    Dim foundCell As Range
    
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
    
    ' Get the value to find from Sheet2, Cell C2
    valueToFind = ws2.Range("C2").Value
    
    ' Set the search range to Sheet1, Column B
    Set searchRange = ws1.Range("B:B")
    
    ' Search for the value in the search range
    Set foundCell = searchRange.Find(what:=valueToFind, LookIn:=xlValues, lookat:=xlWhole)
    
    If foundCell Is Nothing Then
        ' If the value is not found, display an error message on Sheet2, Cell E3
        ws2.Range("E3").Value = "Value not found in Sheet1"
    Else
        ' If the value is found, add text to the corresponding row in Sheet1, Column L
        ws1.Range("L" & foundCell.Row).Value = "Text added"
    End If
    
End Sub

 

[kottn]

Try this

Sub CheckValues()

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim valueToFind As Variant
    Dim searchRange As Range
    Dim foundCell As Range
   
    Set ws1 = ThisWorkbook.Worksheets("Sheet1")
    Set ws2 = ThisWorkbook.Worksheets("Sheet2")
   
    ' Get the value to find from Sheet2, Cell C2
    valueToFind = ws2.Range("C2").Value
   
    ' Set the search range to Sheet1, Column B
    Set searchRange = ws1.Range("B:B")
   
    ' Search for the value in the search range
    Set foundCell = searchRange.Find(what:=valueToFind, LookIn:=xlValues, lookat:=xlWhole)
   
    If foundCell Is Nothing Then
        ' If the value is not found, display an error message on Sheet2, Cell E3
        ws2.Range("E3").Value = "Value not found in Sheet1"
    Else
        ' If the value is found, add text to the corresponding row in Sheet1, Column L
        ws1.Range("L" & foundCell.Row).Value = "Text added"
    End If
   
End Sub

Works brilliantly, thank you so much !

 

[mehidy1437]

If you received at least one reply answering your question and solving your problem,
then please remember to click the "Mark as solution" button - - right next to the best post which is answering your question.

 

[mehidy1437]

Glad it works, thanks for your feedback.