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 (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_contrib_faq_item)

[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.

:rtfm:

[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

[estatefinds]

Any help on this from anyone is appreciated, sincerely. Thank you

[estatefinds]

Any help on this from anyone is appreciated, Sincerely. Thank you!

[SamT]

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

Location = Split(Found.Address, "$")
Nah, he just can't figure out how to replace the line giving him problems with the new line of code offered. Even when given explicit directions.

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


]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.

Even when told the reasons why the bad line of code was 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 $

[SamT]

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

I dont know why Im having such troubleBecause you don't pay attention to the experts.

Array is a Function. What you are trying is like saying "Sum = 1+2" instead of X =Sum(1, 2).
And the Split Function doesn't work like that either.

The Array Function returns an array of the elements inside the Parentheses

Dim SomeVariable As Variant 'As Variant is required
SomeVariable = Array("A", 1, "Let there be Light", Sheets(1).Range("A1"))
'Now, the Array, SomeVariable, contains 4 elements: 2 Strings, a number and an Object

[estatefinds]

There was some confusion, but I got it! It works great! Thank you!!:)
i appreciate it!