Help with Macro for Entering value in a cell

laxminarayana

Board Regular
Joined
Nov 16, 2013
Messages
54
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.

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
Code:
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")
 

Chris Macro

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
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

Board Regular
Joined
Nov 16, 2013
Messages
54
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

Well-known Member
Joined
Nov 2, 2011
Messages
1,341
Maybe try :

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

or you could put this code at the beginning:

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

Not entirely sure what you're going for.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,142
Messages
5,599,971
Members
414,353
Latest member
ljhan

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