Conditional search and notify in different sheet

[nehachoubey]

Hi,
I have a sheet names SOURCE. I have to check in the C column of SOURCE sheet whether '~' sign exists at both the ends and notify Y/N in the Col B of the VALIDATE sheet. No. of cells in SOURCE(C) is not known. Plz help with a macro..

 

[VoG]

Try

Sub Notify()
Dim LR As Long, i As Long
With Sheets("SOURCE")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        Sheets("VALIDATE").Range("B" & i).Value = IIf(Left(.Range("C" & i).Value, 1) = "~" And Right(.Range("C" & i).Value, 1) = "~", "Y", "N")
    Next i
End With
End Sub

 

[nehachoubey]

Thanks alot but I have to start from B3 cell. If i give B, the code is checking first 2 cells unnecessarily

 

[VoG]

Try

Sub Notify()
Dim LR As Long, i As Long
With Sheets("SOURCE")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 3 To LR
        Sheets("VALIDATE").Range("B" & i).Value = IIf(Left(.Range("C" & i).Value, 1) = "~" And Right(.Range("C" & i).Value, 1) = "~", "Y", "N")
    Next i
End With
End Sub

 

[nehachoubey]

Thanks . One more thing.. if the cell is null then nothing shud be printed. Here in this case it is printing according to the last cell.

 

[VoG]

Try

Sub Notify()
Dim LR As Long, i As Long
With Sheets("SOURCE")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 3 To LR
        If .Range("C" & i).Value <> "" Then
            Sheets("VALIDATE").Range("B" & i).Value = IIf(Left(.Range("C" & i).Value, 1) = "~" And Right(.Range("C" & i).Value, 1) = "~", "Y", "N")
        End If
    Next i
End With
End Sub

 

[nehachoubey]

thanks for the reply. its working properly.Pls check the following code:
<code>
Dim LR As Long, i As Long
Dim i1, i2 As Integer

Sub validate()
i1 = 0
With Sheets("CSTB_DATA_DICTIONARY")
LR = .Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
i2 = i1 + 7
If Mid(Worksheets("CSTB_DATA_DICTIONARY").Range("B" & i).Value, 5, 1) <> UCase("s") Then
Sheets("VALIDATE").Range("A" & i2).Value = i1 + 1
Sheets("VALIDATE").Range("B" & i2).Value = "CSTB_DATA_DICTIONARY"
Sheets("VALIDATE").Range("C" & i2).Value = "Pls Use Synonym for Tablename# " & Sheets("CSTB_DATA_DICTIONARY").Range("B" & i).Value
End If
i1 = i1 + 1
Next i
End With
</code>


This code is entering null values when the condition is satisfying ie if the 5th letter is 'S'. what should i do to avoid those null rows? Thanks in advance