VBA to Find the value in the next column of the cell having the required string in the array

[Raghava@rock]

Hi Team,


I am looking for sample vba code which will provide me the value of Column B, while the text in column A matches with specific text which searching through ARRAY.

Currently below is the code i am using , if the edit can be made to the same that would great.

once the Find text is found in MyArray then need to get teh value that is there in column C.

Dim MyArray()       As String
    Dim n               As Long
    Dim i               As Long
    Dim IsThere         As Boolean
    Dim FindText        As String
    
    n = Sheets("Sheet1").Range("B65536").End(xlUp).Row
    ReDim MyArray(1 To n)
    For i = 1 To n
        MyArray(i) = Range("B" & i).Text
    Next i
    
    FindText = "Name"
    For i = 1 To n
        If MyArray(i) = FindText Then
             :Need to get the value in column c in the same row
            Exit For
        End If
    Next i

 

[Hui]

Raqhava

try this:

Sub test()
  Dim MyArray()  As Variant
  Dim n  As Long
  Dim i  As Long
  Dim IsThere  As Boolean
  Dim FindText  As String
   
  n = Sheets("Sheet1").Range("B65536").End(xlUp).Row
  MyArray = Range("B1:C" & CStr(n)) 'Noet I have included Column C here
   
  FindText = "Name"
  For i = 1 To n
  If MyArray(i, 1) = FindText Then
  MsgBox "Column C contains: " + CStr(MyArray(i, 2))
  Exit For
  End If
  Next i
End Sub

Note I have included Column C in the array
The array is loaded in one line also, no need for loops

 

[NARAYANK991]

Hi ,

Another way :

Public Sub FindText()
           Dim MyArray    As Variant
           Dim IsThere    As Variant
           Dim lastrow    As Long
           Dim FindText   As String

           On Error Resume Next
      
           With Sheets("Sheet1")
                lastrow = .Range("B65536").End(xlUp).Row
                MyArray = .Range("B2:B" & lastrow).Value
    
                FindText = "Name"

                IsThere = Application.Match(FindText, MyArray, 0)
                If VBA.IsError(IsThere) Then
                   MsgBox FindText & " not found !", vbExclamation
                Else
                   MsgBox "Column C has the data : " & .Range("C2").Offset(IsThere - 1).Value
                End If
           End With
End Sub

Narayan