InStr problem

[Alexandro88]

If [COLOR="Red"]Myday = InStr(1, Left(rngcell.Value, 5), "Sun" Or "Sat" Or "Mon" Or "Tue" Or "Wed" Or "Thu" Or "Fri")[/COLOR] Then
         If InStr(1, Left(rngcell.Value, 3), "LRR") Then
            Cells.Value = lr_Total
         ElseIf InStr(1, Left(rngcell.Value, 3), "OP3") Then
            Cells.Value = zResult_Total_NSOP
         ElseIf InStr(1, Left(rngcell.Value, 3), "OP4") Then
            Cells.Value = vResult_Total_NSOP
         ElseIf InStr(1, Left(rngcell.Value, 3), "OL3") Then
            Cells.Value = vResult_Total_NSOL
         ElseIf InStr(1, Left(rngcell.Value, 3), "OL4") Then
            Cells.Value = zResult_Total_NSOL
         End If
    End If

I get erro for the red line. Can anyone help me and provide solution to me?

 

[VoG]

Perhaps

Myday = Left(rngcell.Value, 3)
If Myday = "Sun" Or Myday = "Sat" Or Myday = "Mon" Or Myday = "Tue" Or Myday = "Wed" Or Myday = "Thu" Or Myday = "Fri" Then

 

[Andrew Poulsom]

What are you trying to do exactly? You will have to repeat for each string eg:

If Myday = InStr(1, Left(rngcell.Value, 5), "Sun") Or Myday = InStr(1, Left(rngcell.Value, 5), "Sat") Then

 

[Alexandro88]

LValue = Format(Cells(1, 17).Value, "d - mmm - yy")
Myday = Format(LValue, "dddd")

 
If Myday = InStr(1, Left(rngcell.Value, 5), "Sun" Or "Sat" Or "Mon" Or "Tue" Or "Wed" Or "Thu" Or "Fri") Then
         If InStr(1, Left(rngcell.Value, 3), "LRR") Then
            Cells.Value = lr_Total
         ElseIf InStr(1, Left(rngcell.Value, 3), "OP3") Then
            Cells.Value = zResult_Total_NSOP
         ElseIf InStr(1, Left(rngcell.Value, 3), "OP4") Then
            Cells.Value = vResult_Total_NSOP
         ElseIf InStr(1, Left(rngcell.Value, 3), "OL3") Then
            Cells.Value = vResult_Total_NSOL
         ElseIf InStr(1, Left(rngcell.Value, 3), "OL4") Then
            Cells.Value = zResult_Total_NSOL
         End If
 End If

I want to find column that match with the date.

 

[Andrew Poulsom]

What are your column headings - just those days?

 

[Alexandro88]

Yes.

I want to find which column date that equal with actual date.
Then i want pump in the data in that column

 

[Andrew Poulsom]

You can use the Match WorksheetFunction to find the position of a value within a range. I don't know where your range is , so I'll post an example that uses an array:

Sub Test()
    Dim Days As Variant
    Dim Myday As String
    Dim Col As Long
    Days = Array("Sat", "Sun", "Mon", "Tue", "Wed", "Thu", "Fri")
    Myday = Format(Cells(1, 17).Value, "ddd")
    Col = WorksheetFunction.Match(Myday, Days, False)
    MsgBox Col
End Sub

 

[Alexandro88]

can't because i need to loop through every column and row to find that match day

 

[Andrew Poulsom]

I don't see any looping in the code you posted. Where are the headings (sheet and range reference)?

 

[Alexandro88]

Worksheets("Calc").Activate

For Each rngcell In Range("A1:DI" & Range("A" & Rows.Count).End(xlUp).Row)
If Myday = InStr(1, Left(rngcell.Value, 5), "Sun" Or "Sat" Or "Mon" Or "Tue" Or "Wed" Or "Thu" Or "Fri") Then
If InStr(1, Left(rngcell.Value, 3), "LRR") Then
Cells.Value = lr_Total
ElseIf InStr(1, Left(rngcell.Value, 3), "OP3") Then
Cells.Value = zResult_Total_NSOP
ElseIf InStr(1, Left(rngcell.Value, 3), "OP4") Then
Cells.Value = vResult_Total_NSOP
ElseIf InStr(1, Left(rngcell.Value, 3), "OL3") Then
Cells.Value = vResult_Total_NSOL
ElseIf InStr(1, Left(rngcell.Value, 3), "OL4") Then
Cells.Value = zResult_Total_NSOL
End If
End If

Next