VBA Code to Find Row Number and Copy it to Adjacent Cell

[theteerex]

With two sheets, a Feeder sheet and a Database, I have two columns of data I would like matched.
On the Feeder, all data is in rows:
VALUE 1 -Location Time Cost Duration
VALUE 2 -Location Time Cost Duration
VALUE 3 -Location Time Cost Duration
VALUE 4 -Location Time Cost Duration

On Database, all data is in columns:
VALUE 1 Location
VALUE 1 Time
VALUE 1 Cost
VALUE 1 Duration
VALUE 2 Location
VALUE 2 Time
VALUE 2 Cost
VALUE 2 Duration
VALUE 3 Location
VALUE 3 Time
VALUE 3 Cost
VALUE 3 Duration

So I can run pivot table.

I would like my code to find the first instance of each value(a text string) in the Database and copy the row number in the adjacent cell on the Feeder sheet.
So my data would end up looking like this on the Feeder sheet:
2 VALUE 1 -Location Time Cost Duration
6 VALUE 2 -Location Time Cost Duration
10 VALUE 3 -Location Time Cost Duration
14 VALUE 4 -Location Time Cost Duration

This way, a user can always see that their records have been correctly created in the Database without having to run a search. It also serves as an error checker.
I would like this to run automatically.
Any suggestions?

Thanks in advance!

 

[mumps]

On the Feeder sheet you say

I have two columns of data

Is "Value 1" in column A and "Location Time Cost Duration" in column B?
On the database sheet you say

all data is in columns

Is "Value 1" in column A and "Location" in column B? Please clarify what data is in which columns on each sheet.

 

[Nine Zero]

Try this out..... assuming the following

Your feeder sheet has data like so

	Location	Time	Cost	Duration
A
B
C
D
E

<tbody>
</tbody>

Your database has data like so

A

A

A

A

B

B

B

B

C

C

C

C

D

D

D

D

E

E

E

E

House

1

2

3

School

4

5

6

Work

7

8

9

Tent

10

11

12

Stuff

13

14

15

<colgroup><col width="64" span="20" style="width:48pt"> </colgroup><tbody>
</tbody>

and all data begins at A1

Sub Stuff()
Dim fr, dr As Range
Dim lCol, lRow, counter As Integer
Dim fd, db As Worksheet


Set fd = Sheets("Feeder")
Set db = Sheets("Database")
lRow = fd.Cells(Rows.Count, 1).End(xlUp).Row
lCol = db.Cells(1, Columns.Count).End(xlToLeft).Column


For Each fr In Range("A2:A" & lRow)
  counter = 1
    For Each dr In db.Range(db.Cells(1, 1), db.Cells(1, lCol))
        If dr.Value = fr.Value Then
            fr.Offset(, counter).Value = dr.Offset(1, 0).Value
            counter = counter + 1
        End If
    Next dr
Next fr
End Sub

To achieve results like this in your feeder sheet

	Location	Time	Cost	Duration
A	House	1	2	3
B	School	4	5	6
C	Work	7	8	9
D	Tent	10	11	12
E	Stuff	13	14	15

<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[theteerex]

Try this out..... assuming the following

Your feeder sheet has data like so

	Location	Time	Cost	Duration
A
B
C
D
E

<tbody>
</tbody>

Your database has data like so

A

A

A

A

B

B

B

B

C

C

C

C

D

D

D

D

E

E

E

E

House

1

2

3

School

4

5

6

Work

7

8

9

Tent

10

11

12

Stuff

13

14

15

<tbody>
</tbody>

Actually, my Feeder is like this:

	B	C	D	E
1	Value1	1	2	3
2	Value2	4	5	6
3	Value3	7	8	9
4	Value4	10	11	12
5	Value5	13	14	15

<tbody>
</tbody>

And in my Database, it is like this:

	A	B	C	D
1	Name	Category	Price
2	Value1	Location	1
3	Value1	Time	2
4	Value1	Cost	3
5	Value2	Location	4

<tbody>
</tbody>

So I can run pivot table. I want the row numbers to appear in column A of my Feeder sheet.

 

[Nine Zero]

Actually, my Feeder is like this:

	B	C	D	E
1	Value1	1	2	3
2	Value2	4	5	6
3	Value3	7	8	9
4	Value4	10	11	12
5	Value5	13	14	15

<tbody>
</tbody>

And in my Database, it is like this:

	A	B	C	D
1	Name	Category	Price
2	Value1	Location	1
3	Value1	Time	2
4	Value1	Cost	3
5	Value2	Location	4

<tbody>
</tbody>

So I can run pivot table. I want the row numbers to appear in column A of my Feeder sheet.

Ok so in that case your cell A1 in Feeder would have the value 2 and cell A2 would have 5 and everything in between those blank? and so on?

 

[mumps]

Try:

Sub FindRow()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Feeder").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    Dim foundRng As Range
    For Each rng In Sheets("Feeder").Range("B1:B" & LastRow)
        Set foundRng = Sheets("Database").Range("A:A").Find(rng, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundRng Is Nothing Then
            rng.Offset(0, -1) = foundRng.Row
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Cross posted at: https://www.excelforum.com/excel-pr...-row-number-and-copy-it-to-adjacent-cell.html

 

[Fluff]

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[theteerex]

Thanks for pointing that out.

 

[theteerex]

Ok so in that case your cell A1 in Feeder would have the value 2 and cell A2 would have 5 and everything in between those blank? and so on?

That is correct Nine Zero.