VBA Not InStr

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

The extract of code below is part of a larger script which basically searches for a text value within in a string. Once found relevant cells within the same row are copied and paste to a 'Destination' sheet.


ElseIf InStr(.Offset(i, 0), "OVH") > 0 And RVal > 0 Then
strProject = .Offset(i, -1)
With PRO.Range("B1")
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = strProject
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = strProject Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = strProject
End If
End If


I'm now trying to adapt the first line to say that I only want to copy the data if certain text values are not in the string.

I've done some research and that you can use the 'Not InStr' function, show I changed the first line to read:


ElseIf Not InStr(.Offset(i, 0), "DIR", "Enhancements", "IND", "OVH") > 0 And RVal > 0 Then

But when I run this I receive Run time error '13': Type Mismatch, and I'm not sure why.

I just wondered whether someone may be able to look at this please and let me know whwre I'm going wrong.

Many thanks and kind regards

[p45cal]

if instr(.offset(i,0),"OVH")=0
will return True if the string is not to be found.
You can string them together:
if instr(.offset(i,0),"OVH")=0 and instr(.offset(i,0),"DIR")=0 and instr(.offset(i,0),"Enhancements")=0 and instr(.offset(i,0),"IND")=0 then

[hobbiton73]

Hi @p45cal, thank you for taking the time to reply to my post.

Forgive me for being a little slow, but I've now changed the row to read:


ElseIf InStr(.Offset(i, 0), "DIR", "Enhancements", "IND", "OVH") = 0 And RVal > 0 Then

But unfortunately I'm still receiving the error message. Could you perhaps elaborate a little please, because I'm not sure I've understood.

Many thanks and kind regards

[snb]

Why don't you post a sample workbook and the code you've got so far ?
Remember: partial questions, partial answers....

[p45cal]

I don't think you can put multiple search strings like that in Instr. I suspect you'll have to string them together as suggested in my last post. Do you know different?

[hobbiton73]

Hi @snb, thank you for taking the time to reply to my post.

I tried to stay away from downloading the file because it has a lot of sensitive information, but please find my complete script below.


Sub Extract()
Dim i As Long, j As Long, m As Long, strProject As String, RDate As Date, RVal As Single
Dim BlnProjExists As Boolean, DI As Worksheet, EH As Worksheet, IND As Worksheet, OVH As Worksheet, PRO As Worksheet
Application.ScreenUpdating = 0

Set DI = Sheets("Direct Activities")
Set EH = Sheets("Enhancements")
Set IND = Sheets("Indirect Activities")
Set OH = Sheets("Overheads")
Set PRO = Sheets("Projects")


DI.Rows("5:" & Rows.Count).Clear
EH.Rows("5:" & Rows.Count).Clear
IND.Rows("5:" & Rows.Count).Clear
OH.Rows("5:" & Rows.Count).Clear
PRO.Rows("5:" & Rows.Count).Clear


With Sheets("AllData").Range("E3")
For i = 1 To .CurrentRegion.Rows.Count - 1
strProject = .Offset(i, 0)
RDate = .Offset(i, 3)
RVal = .Offset(i, 4)
If InStr(.Offset(i, 0), "DIR", "Enhancements", "IND", "OVH") = 0 Then
strProject = .Offset(i, 0)


With EH.Range("B1")
If .CurrentRegion.Rows.Count = 1 Then
.Offset(1, 0) = strProject
j = 1
Else
BlnProjExists = False
For j = 1 To .CurrentRegion.Rows.Count - 1
If .Offset(j, 0) = strProject Then
BlnProjExists = True
Exit For
End If
Next j
If BlnProjExists = False Then
.Offset(j, 0) = strProject
End If
End If
Select Case Format(RDate, "mmm yy")
Case "Apr 13"
m = 1
Case "May 13"
m = 2
Case "Jun 13"
m = 3
Case "Jul 13"
m = 4
Case "Aug 13"
m = 5
Case "Sep 13"
m = 6
Case "Oct 13"
m = 7
Case "Nov 13"
m = 8
Case "Dec 13"
m = 9
Case "Jan 14"
m = 10
Case "Feb 14"
m = 11
Case "Mar 14"
m = 12
End Select
.Offset(j, m) = .Offset(j, m) + RVal
End With

End If
Next i
End With
Application.ScreenUpdating = True
End Sub


I really hope this helps.

Many thanks and kind regards

[hobbiton73]

Hi @p45cal, thank you for coming back to me with this. I'm also unsure whether you can link the 'InStr' together'. I think I'll have to do a little more research.

Many thanks and kind regards

[snb]

To start with:


Sub M_snb()
For each sh in Sheets(Array("Direct Activities","Enhancements","Indirect Activities","Overheads","Projects"))
sh.usedrange.offset(4).clearcontents
Next
End Sub


To continue


Sub M_snb_001()
sp=sheets("Enhancements").usedrange
sn=Sheets("AllData").usedrange

for j=4 to ubound(sn)
if instr(sn(j,5),"DIR")+instr(sn(j,5),"Enhancements")+instr(sn(j,5),"IND")+instr(sn(j,5),"OVDH")=0 then
if ubound(sp)=1 then
sheets("enhancements").cells(2,2)=sn(j,5)
else
x=application.match(sn(j,5),application.index(sp,0,1),0)
sp(x,choose(month(sn(j,8)) mod 12 +1)=sn(j,9)
end if
End If
Next

sheets("Enhancements").usedrange=sp
End With

[hobbiton73]

Hi @snb, thank you very much for this.

Kind Regards

Chris

[hobbiton73]

All, thank you very much for your time and trouble.

I'm not sure whether this is the most effective way of doing this, but I found a solution here: http://stackoverflow.com/questions/3274760/vba-for-excel-question-how-to-make-vba-search-for-multiple-texts-in-ranges, so I've changed my row to:


ElseIf InStr(.Offset(i, 0), "DIR") = 0 And InStr(.Offset(i, 0), "Enhancements") = 0 And InStr(.Offset(i, 0), "IND") = 0 And InStr(.Offset(i, 0), "OVH") = 0 And RVal > 0 Then and this seems to work.

As I say, I'm relatively new to VBA, so there may be a better way to do this.

Many thanks and kind regards

[p45cal]

relatively new to VBA, so there may be a better way to do thisperhaps using snb's neater:

ElseIf InStr(.Offset(i, 0), "DIR") + InStr(.Offset(i, 0), "Enhancements") + InStr(.Offset(i, 0), "IND") + InStr(.Offset(i, 0), "OVH") = 0 And RVal > 0 Then
and since .Offset(i, 0) is referred to 4 times then it might be faster (if the code's doing this a lot) to reduce the number of times vba has to enumerate it, have:
xx = .Offset(i, 0)somewhere before the next line is executed:

ElseIf InStr(xx, "DIR") + InStr(xx, "Enhancements") + InStr(xx, "IND") + InStr(xx, "OVH") = 0 And RVal > 0 Then

[hobbiton73]

Hi @snb, thank you very much for taking the time to put this together. It's exactly what I was after.

All the best and kind regards.

[hobbiton73]

Hi @p45cal, thank you for coming back to me with this. Yes I too am inclined to go with the solution from @snb.

Many thanks and kind regards