# Newbe need excel search and copy value

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

#### rolf123

Very new to macro vbs. Need to solve a problem, not become an expert.

Wish to search a second excel data table and find line where 2 cell values are equal to 2 cell values in first tables line and then copy the 3:d value in table 2 to table 1 line (1 line table 1 search all lines in table 2, then 2 line table 1 search all lines in table 2 etc.)

Table 1 is a set of deliveries from-to, table 2 distance table with the distance between from and to. Data tables can during run be copied in same spreadsheet (different columns)

Tryed this (for value in columns p-u), did not compile due to errors in code:

Sub loop1()
Dim I As Integer
Dim J As Integer
For I = 1 To 10
For J = 1 To 10
If Cells(x, 16).Value Like Cells(y, 19).Value And Cells(x, 17).Value Like Cells(y, 20).Value Then Cells(y, 21).Copy Cells(x, 18).Paste
Next
Next
End Sub

Thankful for any help

#### 20_2_Many

Welcome to the Forum, Rolf123. Can you please tell us what version of OS and Excel you are using, and using the Go Advanced option below attach a dummy example of the workbook in question? (No Sensitive Data)

Brgds
Rolf

#### Attachments

• 15.6 KB Views: 30

#### 20_2_Many

Rolf - not following you exactly, I think, but looks like if, for example, D20 = E20 and f20 = G 20 then G 20 = B20 or "" (nothing). If that sounds close, I tested just placing 9 in b20, 1 in d20, 1 in e20, 2 in f20 and 2 in G20. If D20& e20 match AND f20&G20 Match, the n from B20 is placed in G20 with the formula
=IF(AND(D20=E20,F20=G20),B20,"")
With 16000 lines, a formula in the destination cells may be quicker, and etaf (who posts here) is awesome with these, but even with a macro, I am mostly checking exactly what you are comparing to what you are comparing to where you want the answers. My fault, I am simply not following your question.

#### rolf123

Hi 20_2_Many,

Thanks for answer. I shouldtry to explain better.

This is the problem: I wantto compute our CO2 footprint, that is how much CO2 emissions we are responsiblefor. To do this I need to add the distance that each transport has traveled tothe main database. The main database consists of lines with transport information,every line is a transport from point A to point B. In every line there isinformation on starting point and end point in form of a 4 digit code. Unfortunatelythe database do not store the travel distance. But, I have access to a seconddatabase where the transport distance between 2 locations is stored (3 cellsper line, code for starting point, code for end point, distance in km).

I need to compare everyline in the main database with every line in the entire distance table and whenthe search finds the right line (cells) (point A = point A, point B = point B) thencopy the distance value from the line (cell 3) in the distance table to themain table. Data in the 2 tables are not on the same lines (ex. Line 345 inmain table could find a match at line 1254 in the distance table).

I have tried severaldifferent ways to do it in Macro but I have to limited knowledge of Macro. Ican´t get any macro to start due to syntax error every time I try.

#### Keebellah

Hans
Hi, one more that joins is
I cannot figure out whihc column(s) in table one are the travel codes.

Table 2 here is I hope not in the same sheet?
Could you indicate whihc column is the point A and which column is poitn B in Table 1?

Table 2 is the translation tabel to get the Kmts?

#### rolf123

Column N= point 1, column W= point2 in example.
The value in column distance should be copied to the right line in column add copy data.

Yes I put table 2 in same sheet. Do not need to be in same sheet. Can be cut and paste to other sheet or excel.

Brgds
Rolf

Hi, one more that joins is
I cannot figure out whihc column(s) in table one are the travel codes.

Table 2 here is I hope not in the same sheet?
Could you indicate whihc column is the point A and which column is poitn B in Table 1?

Table 2 is the translation tabel to get the Kmts?

#### Keebellah

Hans
Well this helps.
I'm no good in formulas but I can write a function for it if it's okay with you, you can then just place the function in the column where you want the distance, pass the two points as parameters and it should work.

If this is okay with you'll I'll try and put it together during the weekend.

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

As Seen On