Macro to Search a list and enter data into a cell on a diffrent worksheet if Criteria is met

[beerw0lf]

Hello All,
I am in need of some assistance once again. I have 2 worksheets. "Data" & "List". In the "Data" sheet column "H" contains model numbers. In the "List" sheet column "A" contain model numbers and column "C" contaims family names. I would like a macro that searches through column "H" in the "Data" sheet and if a model number matches column "A" of the "List" sheet I want the data in column "C" of the "List" sheet copied over to column "F" of the "Data" sheet.

I hope this makes sense.

Any and all help is greatly apprectiated.

Many Thanks!!

 

[ColinKJ]

Hi beerw0lf,

Try this

Code:

Sub coptdata()
r = ThisWorkbook.Sheets(1).Range("A65536").End(xlUp).Row
dr = Workbooks("Data.xls").Sheets(1).Range("H65536").End(xlUp).Row
For a = 1 To r
x = ThisWorkbook.Sheets(1).Cells(a, 1).Text
With Workbooks("Data.xls").Sheets(1)
For b = 1 To dr
If .Cells(b, 8).Text = x Then
.Cells(a, 6) = ThisWorkbook.Sheets(1).Cells(b, 3)
End If
Next b
End With
Next a
End Sub


Code:

Regards

ColinKJ

 

[beerw0lf]

Thanks for the reply ColinKJ. This macro is not working. I'm a little confused. You have "Data.xls" in the macro. I have 2 worksheets called "Data" and "List". The macro is hanging up on the line:

dr = Workbooks("Data.xls").Sheets(1).Range("H65536").End(xlUp).Row


Thanks

 

[ColinKJ]

Hi beerw0lf,

Sorry, I should have said, the macro should be in the List.xls workbook.

Reference in the macro to "ThisWorkBook" is List.xls.

If you are running the macro in a seperate workbook, change "ThisWorkBook" to "Workbooks("List.xls")"

ColinKJ

 

[beerw0lf]

ADVERTISEMENT

This macro will be running in just 1 workbook "ST Family.xls". Within this workbook I will be working with 2 worksheets "Data" and "List". I think I may be missing something here and not understanding the macro.

Thanks

 

[ColinKJ]

Hi beerw0lf,

Sorry again, I'm having a bad day, it's not your understanding, it's mine.

I was reading your post as you having two Workbooks, where in fact you have two worksheets in the same workbook, sorry.

Try this

Code:

Sub coptdata()
r = Sheets("List").Range("A65536").End(xlUp).Row
dr = Sheets("Data").Range("H65536").End(xlUp).Row
For a = 1 To r
x = Sheets("List").Cells(a, 1).Text
With Sheets("Data")
For b = 1 To dr
If .Cells(b, 8).Text = x Then
.Cells(a, 6) = Sheets("List").Cells(b, 3)
End If
Next b
End With
Next a
End Sub

Code:

Regards

ColinKJ

 

[beerw0lf]

ADVERTISEMENT

The macro does not error but it also does not seem to accomplish its task. Nothing seems to be copying over.

 

[ColinKJ]

Hi again beerw0lf,

We're not having a good are we!! both of us I mean.

If you can send me a priate mail with an email address, I'll send you the test sheet I'm running in.

Regards

ColinKJ

 

[beerw0lf]

Hello ColinJK,
I believe this is working now. I got the list second hand and apparently there was no matching data beween the list and the data worksheets. As soome as I coppied over matching data it seems to work. Thanks so much for you help with this it is greatly appreciated.