Look up, copy & paste

[mezzy01]

Hi Board,

I've got a bit of a problem here. I've got a bit of code which fails to recognise the Friday condition to display Msg "Friday". Any ideas

And how can I look up the value Sheet1 A1 date in another spread sheet and copy & paste a range from Sheet 1 to the right of the date on say Sheet2 ???

Puzzled

Pls help

Private Sub Update()
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
If Weekday(Worksheets("Sheet1").Range("A1").Value, vbMonday) > 5 Then
Msg = "You can not enter Rates for Saturday or Sunday"
Style = vbOKOnly + vbExclamation
MsgBox Msg, Style

Else
If Weekday(Worksheets("Sheet1").Range("A1").Value, vbMonday) = 6 Then
Msg = "Friday"


Else
Msg = "Do you want to copy the Rates for" & Sheets("Sheet1").Range("A1")
Style = vbYesNo + vbQuestion
Response = MsgBox(Msg, Style, Title, Help, Ctxt)
If Response = vbYes Then
Msg = "Test"
Else
MyString = "No" '
End If
End If
End If
End Sub

 

[just_jon]

You are assigning the value "Friday" to the variant msg, but then you're not doing anything with it.

BTW, all your variables are of type Variant - should be explicitly declared as in

Dim msg as String

 

[mezzy01]

Friday

Thanks for the reply

I've change the code to

If Weekday(Worksheets("Sheet1").Range("A1").Value, vbMonday) > 6 Then
Msg = "Friday"
Style = vbOKOnly + vbExclamation
MsgBox Msg, Style

but it still doesn't work

Any ideas

 

[just_jon]

<font face=Courier New><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Update()
<SPAN style="color:#00007F">Dim</SPAN> Msg, Style, Title, Help, Ctxt, Response, MyString
<SPAN style="color:#00007F">If</SPAN> Weekday(Worksheets("Sheet1").Range("A1").Value, vbMonday) > 5 <SPAN style="color:#00007F">Then</SPAN>
    Msg = "You can not enter Rates for Saturday or Sunday"
    Style = vbOKOnly + vbExclamation
    MsgBox Msg, Style
<SPAN style="color:#00007F">ElseIf</SPAN> Weekday(Worksheets("Sheet1").Range("A1").Value, vbMonday) = 6 <SPAN style="color:#00007F">Then</SPAN>
    Msg = "Friday"
    MsgBox Msg
<SPAN style="color:#00007F">Else</SPAN>
    Msg = "Do you want to copy the Rates for" & Sheets("Sheet1").Range("A1")
    Style = vbYesNo + vbQuestion
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    <SPAN style="color:#00007F">If</SPAN> Response = vbYes <SPAN style="color:#00007F">Then</SPAN>
        Msg = "Test"
        MsgBox Msg
    <SPAN style="color:#00007F">Else</SPAN>
        MyString = "No"
        MsgBox MyString
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

Is this what you're trying to do?

Might also want to look into Select Case syntax.