Help with Macro for Entering value in a cell

[laxminarayana]

Hi,

In cell "B8" of worksheet1 contains a date. And in worksheet3 there is a list of Dates. what i am trying to do is when the date in cell "B8" is in the list of dates in sheet3, i want to insert a line in sheet2. And if date in cell "B8"
is not present in the dates list of worksheet3 i want to insert a different line. I have written the following code, but it is not doing anything. Can somebody help me with this.

Sub smarttext()
Dim ws1 As Worksheet:   Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet:   Set ws2 = Sheets("Sheet2")
Dim ws3 As Worksheet:   Set ws3 = Sheets("Sheet3")


If ws1.Range("B8").Value = ws3.Range("D12:D28").Value Then
   If ws2.Range("D1").Value = "" Then
   ws2.Range("D1").Value = "Please assign the documents after two days"
   Else
   ws2.Range("D1").Value = ws2.Range("D1").Value & vbLf & vbLf & "Please assign the documents after two days"
   End If
Else
   If ws2.Range("D1").Value = "" Then
   ws2.Range("D1").Value = "Please assign the documents"
   Else
   ws2.Range("D1").Value = ws2.Range("D1").Value & vbLf & vbLf & "Please assign the documents"
   End If
End If
End Sub

 

[Chris Macro]

If ws1.Range("B8").Value = ws3.Range("D12:D28").Value Then

I don't think you can compare a single cell value to muliple values the way you have it coded. I believe you want to cycle through each cell and compare it to Range("B8")

 

[laxminarayana]

Can you please tell me how to do that.

 

[Chris Macro]

Try and see if this works:

<font face=Calibri><SPAN style="color:#00007F">Sub</SPAN> SmartText()<br><br><SPAN style="color:#00007F">Dim</SPAN> ws1 <SPAN style="color:#00007F">As</SPAN> Worksheet:   <SPAN style="color:#00007F">Set</SPAN> ws1 = Sheets("Sheet1")<br><SPAN style="color:#00007F">Dim</SPAN> ws2 <SPAN style="color:#00007F">As</SPAN> Worksheet:   <SPAN style="color:#00007F">Set</SPAN> ws2 = Sheets("Sheet2")<br><SPAN style="color:#00007F">Dim</SPAN> ws3 <SPAN style="color:#00007F">As</SPAN> Worksheet:   <SPAN style="color:#00007F">Set</SPAN> ws3 = Sheets("Sheet3")<br><SPAN style="color:#00007F">Dim</SPAN> String1 <SPAN style="color:#00007F">As</SPAN> String, String2 <SPAN style="color:#00007F">As</SPAN> String<br><SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range, cell <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> myTest <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN><br><br>String1 = "Please assign the documents after two days"<br>String2 = "Please assign the documents"<br><br><SPAN style="color:#00007F">Set</SPAN> rng = ws3.Range("D12:D28")<br>myTest = <SPAN style="color:#00007F">False</SPAN><br><br><SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> cell <SPAN style="color:#00007F">In</SPAN> rng.Cells<br>    <SPAN style="color:#00007F">If</SPAN> ws1.Range("B8").Value = cell.Value <SPAN style="color:#00007F">Then</SPAN><br>        myTest = <SPAN style="color:#00007F">True</SPAN><br>    <br>        <SPAN style="color:#00007F">If</SPAN> ws2.Range("D1").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>            ws2.Range("D1").Value = String1<br>        <SPAN style="color:#00007F">Else</SPAN><br>            ws2.Range("D1").Value = ws2.Range("D1").Value & vbLf & vbLf & String1<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">Next</SPAN> cell<br><br><SPAN style="color:#00007F">If</SPAN> myTest = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>    <SPAN style="color:#00007F">If</SPAN> ws2.Range("D1").Value = "" <SPAN style="color:#00007F">Then</SPAN><br>        ws2.Range("D1").Value = String2<br>    <SPAN style="color:#00007F">Else</SPAN><br>        ws2.Range("D1").Value = ws2.Range("D1").Value & vbLf & vbLf & String2<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[laxminarayana]

THANK YOU very much chris, that helped me a lot.

The code is working.
But when there is no date in cell B8 the macro still performs action. How to avoid that?

Once again Thank You.

 

[Chris Macro]

Maybe try :

  If ws1.Range("B8").Value = cell.Value [B]And ws1.Range(""B8") <> ""[/B] Then

or you could put this code at the beginning:

[B]If ws1.Range(""B8") = "" Then Exit Sub[/B]

Not entirely sure what you're going for.