Return Row - Column for cell addresses

[estatefinds]

Hello, I need the code below to return not only the row for where the data is found but the column, but have it where it shows the Row number. then the "-" then the Letter for example: (232540-J)

Sub mySearch() For Each gCell In Range("AN1", Range("AN5000").End(xlUp))
If IsEmpty(Cells(gCell.Row, 8)) Then
For Each c In Range("E1:AL10000")
i = Application.Match(gCell.Value, Range(c, Cells(325000, c.Column)), 0)
If Not IsError(i) Then
Cells(gCell.Row, 8) = i
Exit For
End If
Next
If IsEmpty(Cells(gCell.Row, 8)) Then
Cells(gCell.Row, 8) = "#N/A"
End If
End If
Next
End Sub

Thank you very much in advnace for help on this!!!

P.S. I had attached a file to view the example.

[SamT]

How long have you been here? And you still haven't learned how to use the # Icon?

Sub mySearch()    
For Each gCell In Range("AN1", Range("AN5000").End(xlUp))
        If IsEmpty(Cells(gCell.Row, 8)) Then
            For Each c In Range("E1:AL10000")
                i = Application.Match(gCell.Value, Range(c, Cells(325000, c.Column)), 0)
                If Not IsError(i) Then
                    Cells(gCell.Row, 8) = i
                    Exit For
                End If
            Next

            If IsEmpty(Cells(gCell.Row, 8)) Then
                Cells(gCell.Row, 8) = "#N/A"
            End If
        End If
    Next
End Sub

[SamT]

So I refactored your code, meaning I did not change what it does, just made it more "Best Practice" so I could understand it

Sub mySearch()    
Dim gCell As Range
Dim c As Range
Dim i As Variant
Dim r as Long

For r = 1 to Cells(Rows.Count, "AN").End(xlUp.Row

          If IsEmpty(Cells(r, "H")) Then
               For Each c In Range(Range("E1"), Cells(Rows.Count, "AL").End(xlUp))
'Holy Loops Batman! For every cell in 34 columns, Find a Match in the rest of the column below that cell! Of course that only happens when there is no matching value.
'You are lucky that Excel goes across, then down
                   i = Application.Match(Cells(r, "AN").Value, Range(c, c.End(xlDown)), 0)
                   If Not IsError(i) Then 
                        Cells(r, "H") = i
 'i = the number of rows below c.Row
'the Column = c.Column
                        Exit For
                   End If
              Next
         End If

         If IsEmpty(Cells(r, "H")) Then Cells(r, "H") = "#N/A"
Next
End Sub

[estatefinds]

What do you mean about the hash tag? above in post 1, I
used the "-" for a dash in between the row number and column letter.

I under stand the where you put the H for the 8, I meant to change that to 41 so that would be AO.
is there a way to get add to macro to get the row number with the column?

[SamT]

This is how I would do it. (complied but not tested)

Option Explicit

Sub mySearch()
Dim colH As Range
Dim colAN As Range
Dim DataTable As Range
Dim Found As Range
Dim Location As Variant
Dim rw As Long

Set colH = Range("H:H")
Set colAN = Range("AN:AN")
Set DataTable = Range(Range("E1"), Cells(Rows.Count, "AL").End(xlUp))

For rw = 1 To Cells(Rows.Count, "AN").End(xlUp).Row
  With colH
    If IsEmpty(.Cells(rw)) Then
      Set Found = DataTable.Find(colAN.Cells(rw))
      If Found Is Nothing Then
        .Cells(rw) = "#NA"
      Else
        Location = Split(Found.Address, "$")
        .Cells(rw) = Location(0) & "-" & Location(1)
      End If
    End If
  End With
  Set Found = Nothing
Next
End Sub

[estatefinds]

I ran it and it only returns the

-E
-E
-O
-X
-E
-E
-E
-X
in the AO column.
just need row in cluded before the Dash

Sub mySearch()
Dim colAO As Range
Dim colAN As Range
Dim DataTable As Range
Dim Found As Range
Dim Location As Variant
Dim rw As Long
     
Set colAO = Range("AO:AO")
Set colAN = Range("AN:AN")
Set DataTable = Range(Range("E1"), Cells(Rows.Count, "AL").End(xlUp))
     
For rw = 1 To Cells(Rows.Count, "AN").End(xlUp).Row
With colH
If IsEmpty(.Cells(rw)) Then
Set Found = DataTable.Find(colAN.Cells(rw))
If Found Is Nothing Then
.Cells(rw) = "#NA"
Else
Location = Split(Found.Address, "$")
.Cells(rw) = Location(0) & "-" & Location(1)
End If
End If
End With
Set Found = Nothing
Next
End Sub

[SamT]

.Cells(rw) = Location(2) & "-" & Location(1)

See? I selected that line, then clicked the # icon on the menu.

Try it, then click the ^A/A Icon to view the Source of your post

My bad...
The first "slot" of Location Array is empty
Array = Split ("$A$1", "$")
Array(0) =character(s) before first $
Array(1) = Character(s) after first $
Array(2) = character(s) after next $

[estatefinds]

I'm kinda stuck where I put post #7

[mdmackillop]

Please read this on posting code

[estatefinds]

Sub mySearch()
    Dim colAO As Range
    Dim colAN As Range
    Dim DataTable As Range
    Dim Found As Range
    Dim Location As Variant
    Dim rw As Long

    Set colAO = Range("AO:AO")
    Set colAN = Range("AN:AN")
    Set DataTable = Range(Range("E1"), Cells(Rows.Count, "AL").End(xlUp))

    For rw = 1 To Cells(Rows.Count, "AN").End(xlUp).Row
        With colH
            If IsEmpty(.Cells(rw)) Then
                Set Found = DataTable.Find(colAN.Cells(rw))
                If Found Is Nothing Then
                    .Cells(rw) = "#NA"
                Else
                    Location = Split(Found.Address, "$")
                    .Cells(rw) = Location(0) & "-" & Location(1)
                End If
            End If
        End With
        Set Found = Nothing
    Next
 End Sub

[estatefinds]

I went ahead and did the # and I now see what you mean, Thank you . Im stuck on where to place the code below to to get the row location placed before the dash that is before the Column Letter. for example: (232540-J) or (5-E) etc.

My bad...
The first "slot" of Location Array is empty

 Array = Split ("$A$1", "$")
 Array(0) =character(s) before first $
 Array(1) = Character(s) after first $
 Array(2) = character(s) after next $

in the code.

[SamT]

I'm kinda stuck where I put post #7

Replace the line that looks almost exactly like it with it.

Replace the line that you said wasn't working right with it

Read the code that isn't working right and it will be obvious.

[SamT]

I went ahead and did the # and I now see what you mean, Thank you . Im stuck on where to place the code below to to get the row location placed before the dash that is before the Column Letter. for example: (232540-J) or (5-E) etc.

My bad...
The first "slot" of Location Array is empty

 Array = Split ("$A$1", "$")
 Array(0) =character(s) before first $
 Array(1) = Character(s) after first $
 Array(2) = character(s) after next $

in the code.

That is a HELP snippet, to HELP you understand the use of "Split" and Arrays.

[snb]

Can someone please ask the TS to change the title of this thread into something meaningful (for present and coming visitors) ?

[mdmackillop]

Can someone please ask the TS to change the title of this thread into something meaningful (for present and coming visitors) ?

Changed as requested.

[estatefinds]

Ok, I'm working on it, thank you!

[estatefinds]

I dont know why Im having such trouble

Else
                    Array = Split(Found.Adress "$A$1", "$")
                    .Cells(rw) = Location(0) & "-" & Location(1)
                    Next
                End If

Im getting syntax on the Array= Split

[mdmackillop]

typo: Address
should "Location" be "Array"?

[estatefinds]

Else
                    Array = Split (Found.Address"$A$1", "$")
                    .Cells(rw) = Array(0) & "-" & Array(1)
                End If

so Im adding taking away and getting same errors

I dont understand this concept, this array

i looked in my book, programming with microsoft visual basic 2015 and cant find anything in book that could help me figure this out.


need some insight on this , im just having trouble putting it together

[estatefinds]

Sub mySearch() 
    Dim colAO As Range 
    Dim colAN As Range 
    Dim DataTable As Range 
    Dim Found As Range 
    Dim Location As Variant 
    Dim rw As Long 
     
    Set colAO = Range("AO:AO") 
    Set colAN = Range("AN:AN") 
    Set DataTable = Range(Range("E1"), Cells(Rows.Count, "AL").End(xlUp)) 
     
    For rw = 1 To Cells(Rows.Count, "AN").End(xlUp).Row 
        With colH 
            If IsEmpty(.Cells(rw)) Then 
                Set Found = DataTable.Find(colAN.Cells(rw)) 
                If Found Is Nothing Then 
                    .Cells(rw) = "#NA" 
                Else 
                    Location = Split(Found.Address, "$") 
                    .Cells(rw) = Location(0) & "-" & Location(1) 
                End If 
            End If 
        End With 
        Set Found = Nothing 
    Next 
End Sub