0

Problem Description: Find a instr(which is number) then check if the instr value carrying any character front of it or not.(for ex. 123, x123). If instr or instr with character matches the value from sheet 2 then msg box yes.

Problem: I can find the instr but i can't check for if the instr containing any char front of it or not. How do i check it, is it with left(chr) function or something. Here is my code but all is it doing just finding instr not the chr with the instr.

  Sub tym()

Dim ws1 As Worksheet, wb As Workbook, ws2 As Worksheet
Dim b, c As Range, rngNums As Range, rngText As Range
Dim dNums, dText, rN As Long, rT As Long, t, m

Set wb = ActiveWorkbook
Set ws1 = wb.Worksheets("Sheet1")
Set ws2 = wb.Worksheets("Sheet2")
Set c = wb.Worksheets("Sheet3").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Set rngNums = ws1.Range(ws1.Range("A1"), ws1.Cells(Rows.Count, 1).End(xlUp))
dNums = rngNums.Value
Set rngText = ws2.Range(ws2.Range("A1"), ws2.Cells(Rows.Count, 1).End(xlUp))
dText = rngText.Value

For rN = 1 To UBound(dNums, 1)
    b = "-" & dNums(rN, 1) & "-" (right here im defining to find instr but how do i add a function to find char front of instr)
    For rT = 1 To UBound(dText, 1)
        If InStr(1, dText(rT, 1), b) > 0 Then

          MsgBox "yes"

        End If
    Next rT
Next rN

End Sub

1 Answer 1

0

enter image description here

There should be no problem. If

Instr(1,BigString,SubString)

returns a 1, then the SubString is at the beginning of the BigString.

For example, look for the substring "Alpha"

Sub dural()
   MsgBox InStr(1, ActiveCell.Value, "Alpha")
End Sub

enter image description here

1
  • Na i know its work my question is how do i read character front of my instr numbers. i added an image so u can understand. I have 123 and some of the line x123 and i want my code to read both every time when its looping through. Sep 25, 2015 at 3:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .