# InStr problem

#### Alexandro88

##### New Member
Code:
``````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?

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Perhaps

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

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

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

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

Yes.

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

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:

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

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

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

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

Replies
2
Views
116
Replies
4
Views
224
Replies
8
Views
592
Replies
2
Views
178
Replies
3
Views
488

1,203,675
Messages
6,056,683
Members
444,882
Latest member
cboyce44

### 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.

### Which adblocker are you using?

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

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