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

T

theteerex

Board Regular
Joined
Mar 2, 2018
Messages
102
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!
 
Last edited:

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
On the Feeder sheet you say
I have two columns of data
Click to expand...
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
Click to expand...
Is "Value 1" in column A and "Location" in column B? Please clarify what data is in which columns on each sheet.
 
Last edited:
Upvote 0
Try this out..... assuming the following

Your feeder sheet has data like so

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Cost[/TD]
[TD="width: 64"]Duration[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Your database has data like so

[TABLE="class: grid, width: 1280"]
<colgroup><col width="64" span="20" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]School[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD]Work[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD]Tent[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD]Stuff[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]

and all data begins at A1

Code: 
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

[TABLE="class: grid, width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Cost[/TD]
[TD="width: 64"]Duration[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]House[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]School[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]Work[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]Tent[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]Stuff[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Nine Zero said:
Try this out..... assuming the following

Your feeder sheet has data like so

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Location[/TD]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Cost[/TD]
[TD="width: 64"]Duration[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Your database has data like so

[TABLE="class: grid, width: 1280"]
<tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[TD="width: 64"]E[/TD]
[/TR]
[TR]
[TD]House[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD]School[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD]Work[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[TD]Tent[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[TD]Stuff[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
Actually, my Feeder is like this:

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Value2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Value3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Value4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Value5[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15

[/TD]
[/TR]
</tbody>[/TABLE]
And in my Database, it is like this:

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Value1[/TD]
[TD]Location[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Value1[/TD]
[TD]Time[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Value1[/TD]
[TD]Cost[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Value2[/TD]
[TD]Location[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I can run pivot table. I want the row numbers to appear in column A of my Feeder sheet.
 
Upvote 0
theteerex said:
Actually, my Feeder is like this:

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[TD="width: 64"]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Value1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Value2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Value3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Value4[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]12[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Value5[/TD]
[TD="align: right"]13[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]15
[/TD]
[/TR]
</tbody>[/TABLE]
And in my Database, it is like this:

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]A[/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"]C[/TD]
[TD="width: 64"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Name[/TD]
[TD]Category[/TD]
[TD]Price[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Value1[/TD]
[TD]Location[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Value1[/TD]
[TD]Time[/TD]
[TD]2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Value1[/TD]
[TD]Cost[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Value2[/TD]
[TD]Location[/TD]
[TD]4[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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

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?
 
Upvote 0
Try:
Code: 
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
 
Upvote 0
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Simply current workload. Pivot table?
Replies
0
Views
159
ShadowRider
S
M
  • Solved
Sum the values between two specified column headings - number of columns between will change
2
Replies
14
Views
245
Moley84
M
M
  • Question Question
Timestamps - finding & measuring overlaps
Replies
5
Views
140
MichelleH77
M
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
512
ItalianPlatinum
I
D
  • Question Question
Generating Reports in Word Using Data in Excel
Replies
1
Views
335
Dan_W
D

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top