VBA InStr function

Question

HI I am trying to search for a sub string within a string and if the sub string is found then I want to paste the output to my spreadsheet. I am using th InStr function with a conditional statement <> , so that if mt InStr function value is not equal to 0 I know I have a match. I do not get any errors when I run the code but it does not display the result on the sheet. SOS!! :)

Sub unicornhorn()
'Generates user friendly graphs from raw unicorn input data

    Dim columns As Integer
    Dim rows As Integer
    Dim Outputs(50) As String
    Dim LastRow As Integer
    Dim StrG As Integer
    Dim xaxis As Range
    Dim yaxis As Range

    Dim nLeft As Double: nLeft = 20
    Dim nTop As Double: nTop = 20
    Dim start As Double: start = start + 2
    Dim finish As Double: finish = finish + 2


    Dim Curves(14) As String
        Curves(0) = "260nm"
        Curves(1) = "280nm"
        Curves(2) = "214nm"
        Curves(3) = "Cond"
        Curves(4) = "Cond%"
        Curves(5) = "Conc"
        Curves(6) = "pH"
        Curves(7) = "Pressure"
        Curves(8) = "Flow"
        Curves(9) = "Temp"
        Curves(10) = "Fractions"
        Curves(11) = "inject"
        Curves(12) = "logbook"
        Curves(13) = "P960_Press"
        Curves(14) = "P960_Flow"

    Dim D As Worksheet
    Set D = Worksheets("DATA")
    Dim C As Worksheet
    Set C = Worksheets("sheet1")


    'defines data range
    columns = shCurves.Cells(1, shCurves.columns.Count).End(xlToLeft).Column
    'XXreturn check to sheet
    ShData.Cells(5, 5).Value = columns
    rows = shCurves.Cells(shCurves.rows.Count, 1).End(xlUp).Row
    'XXreturn check to sheet
    ShData.Cells(5, 6).Value = rows


    For Z = 0 To columns

        'loops through array for different curves
        Outputs(Z) = shCurves.Cells(2, Z * 2 + 1).Value

        'XXreturn check to sheet
        ShData.Cells(5 + Z, 7).Value = Outputs(Z)

        'Finds last row in column for current curve
        LastRow = Cells(D.rows.Count, Z * 2 + 1).End(xlUp).Row


        For k = 0 To 14

            'finds curve identifyer within string
            StrG = InStr("Outputs(Z)", "Curves(k)")
            If StrG <> 0 Then


            ShData.Cells(25 + k, 5).Value = Curves(k)


            Exit For
            End If                  


        Next k

    Next Z

End Sub

Are you using Option Explicit ? You declare and assign C and D but then go on to use shCurves and ShData — Tim Williams
– Tim Williams, Commented Feb 24, 2016 at 18:28
Just because I'm a noob and I don't really know what I am doing, what is option explicit? also how would you do this most efficiently? — Duranchula
– Duranchula, Commented Feb 25, 2016 at 18:12
Fair enough Mr. Tim! Here's another question for you: So with my array of strings: Curves(0) = "260nm" Cond% and P960_Flow are the only two that do not register a value with the instr function, I realize its because the preceding one in each case shares the beginning characters but how can I fix this? — Duranchula
– Duranchula, Commented Feb 25, 2016 at 18:35
It's difficult to answer this without seeing the headers you're trying to match against. Can you share the workbook? — Tim Williams
– Tim Williams, Commented Feb 25, 2016 at 19:01

donjuedo · Accepted Answer · 2016-08-09 22:45:07Z

4

Why do you have double quotes around your arrays?

StrG = InStr("Outputs(Z)", "Curves(k)")

You're using the string literals, not the variables. Remove the double quotes:

StrG = InStr(Outputs(Z), Curves(k))

edited Aug 9, 2016 at 22:45

donjuedo

2,50520 silver badges28 bronze badges

answered Feb 24, 2016 at 18:30

Gratzy

9,4174 gold badges32 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Duranchula Over a year ago

Thank you, I am still learning so I was unaware that "...." is literal, I though you had to always have those when dealing with strings.

Collectives™ on Stack Overflow

VBA InStr function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related