Using cell reference formula in VBA to return cell to use in VBA code

Question

I am trying to use a formula with INDEX and MATCH to return a cell reference to enter TEXT into with VBA. I have a list of vendors in column A and to find the cell to the right of it I can use the following

=CELL("address";INDEX(A29:C42;MATCH("***";A29:A42;0);2))

However I am struggling with how to get this in my VBA code. (note the value *** is changing as I need to run the sub several times for different vendors. Can i use Function sub for this? I have tried as below with no luck:

Sub CellRef()
'
' CellRef
'
'
    Function.Range(="CELL("ADDRESS";INDEX(A29:C42;MATCH("Accenture";A29:A42;0);2))")

End Sub

JvdV · Accepted Answer · 2018-08-24 07:47:36Z

2

If I understand your question correctly you want to match the value/vendor in column A and return the address of the cell to its right?

You can do like so:

Cell2WorkWith = Cells(Application.Match(Searchvalue, Searchrange, 0),2).Address

If you don't want the "$", then replace them like so:

Cell2WorkWith = Replace(Cells(Application.Match(Searchvalue, Searchrange, 0),2).Address,"$","")

Or, even better, like:

Cell2WorkWith = Cells(Application.Match(Searchvalue, Searchrange, 0),2).Address(0,0)

If I misunderstood and it is the value from that cell to the right then below will do:

Value2WorkWith = Cells(Application.Match(Searchvalue, Searchrange, 0),2).Value

Remember to only use match when the value CAN be found within the range, else you'll have to catch an error.

edited Aug 24, 2018 at 7:47

answered Aug 24, 2018 at 7:28

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

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

3 Comments

mhaverk Over a year ago

Thank you all so much for your help. I will hopefully continue to develop with the assistance of all those that contribute in this community. @QHarr and JvdV special shout out, Thanks so very much.

mhaverk Over a year ago

One further question, how do i use the cell address in the next line to insert the text?

JvdV Over a year ago

You might want to do that directly like so: Cells(Application.Match(Searchvalue, Searchrange, 0),2).Value = "You text goes here!", no need to first get the appropriate cell's range ;)

Pierre44 · Accepted Answer · 2018-08-24 07:43:49Z

One alternative would be to look for the cell with "Find":

Option Explicit

Sub CellRef()

Dim SearchString As String
Dim ra, cell, VendorsRange As Range
Dim k As Integer


Set VendorsRange = Range("E1:E10")
k = 1

For Each cell In VendorsRange

SearchString = cell.Value

    Set ra = Range("A29:A42").Find(What:=SearchString, LookIn:=xlValues, LookAt _
        :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False)

    If ra Is Nothing Then
        MsgBox "String not available"
    Else
        Range("D" & k).Value = ra.Offset(0, 1).Address           'Change "D1" to whereever you want to put your result in
    End If
k = k + 1

Next cell

End Sub

The code would check for every vendor (in my code range "E1:E10"), where is the cell in your range A29:A42 and returns the address of the cell next to it.

Guest · Accepted Answer · 2018-08-24 15:10:27Z

Some people prefer to find all occurrences of a searched item, and then change the value or the formula, or do else. Here is some code allowing great flexibility using an array.

'**************************************************************************************************************************************************************
'To return an array of information (value, formula, address, row, and column) for all the cells from a specified Range that have the searched item as value
'Returns an empty array if there is an error or no data
'**************************************************************************************************************************************************************
Public Function makeArrayFoundCellInfoInRange(ByVal itemSearched As Variant, ByVal aRange As Variant) As Variant
Dim cell As Range, tmpArr As Variant, x As Long

tmpArr = Array()
If TypeName(aRange) = "Range" Then
    x = 0
    For Each cell In aRange
        If itemSearched = cell.Value Then
            If x = 0 Then
                ReDim tmpArr(0 To 0, 0 To 4)
            Else
                tmpArr = reDimPreserve(tmpArr, UBound(tmpArr, 1) + 1, UBound(tmpArr, 2))
            End If
            tmpArr(x, 0) = cell.Value
            tmpArr(x, 1) = cell.Formula
            tmpArr(x, 2) = cell.Address(0, 0) 'Without the dollar signs
            tmpArr(x, 3) = cell.Row
            tmpArr(x, 4) = cell.Column
            x = x + 1
        End If
    Next cell
End If
makeArrayFoundCellInfoInRange = tmpArr
Erase tmpArr

End Function

Collectives™ on Stack Overflow

Using cell reference formula in VBA to return cell to use in VBA code

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related