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/3...exts-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 this

perhaps 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