Macro: Find matching DATA, copy correspondently values into anotherlist

  • Thread starter Thread starter Snoopy
  • Start date Start date
S

Snoopy

Okey guys - please advise me on this.
(There was unfortunately no quick response to my latest problem - and
I begun to swim...
However - that brought me out on deep VBA-water...again - and now need
a life buoy (metaphorical spoken)).

The core of my macro problem is to look for all the values in column A
(Range A5:A??) in Sheet "BOARD" (not bored!!!) that match values in
another list placed in column A in sheet "LIST".
When finding the match I will copy correspondent celle.value in column
D / sheet "BOARD" into column B in sheet "LIST" - this will update my
sheet "LIST" column B values from every new input in sheet "BOARD" /
column D

Refrazing Pink Floyd:
"Is there anybody out there" - that in this case will pull me on
shore.
 
If you want the value in column B of the List sheet to be the corresponding
value in column D of the Board sheet when there's a match in column A of that
same row, you could use a formula like:

=vlookup(a1,board!a:d,4,false)
and drag down the column as far as you need.

If you could have multiple matches in Board column A for a single value in
column A of List and you want the values added to the bottom of column B
(essentially independent lists in column A and column B of sheet List).

Option Explicit
Sub testme()
Dim ListWks As Worksheet
Dim BoardWks As Worksheet
Dim myCell As Range
Dim myRng As Range
Dim Res As Variant
Dim DestCell As Range

Set ListWks = Worksheets("list")
Set BoardWks = Worksheets("Board")

With BoardWks
Set myRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each myCell In myRng.Cells
Res = Application.Match(myCell.Value, ListWks.Range("A:a"), 0)
If IsError(Res) Then
'not found
Else
With ListWks
Set DestCell = .Cells(.Rows.Count, "B").End(xlUp)
If IsEmpty(DestCell.Value) Then
'keep it there
Else
'move down a row
Set DestCell = DestCell.Offset(1, 0)
End If
End With

DestCell.Value = myRng(Res).Offset(0, 3).Value
End If
Next myCell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
If you want the value in column B of the List sheet to be the corresponding
value in column D of the Board sheet when there's a match in column A of that
same row, you could use a formula like:

=vlookup(a1,board!a:d,4,false)
and drag down the column as far as you need.

If you could have multiple matches in Board column A for a single value in
column A of List and you want the values added to the bottom of column B
(essentially independent lists in column A and column B of sheet List).

Option Explicit
Sub testme()
    Dim ListWks As Worksheet
    Dim BoardWks As Worksheet
    Dim myCell As Range
    Dim myRng As Range
    Dim Res As Variant
    Dim DestCell As Range

    Set ListWks = Worksheets("list")
    Set BoardWks = Worksheets("Board")

    With BoardWks
        Set myRng = .Range("A5", .Cells(.Rows.Count, "A").End(xlUp))
    End With

    For Each myCell In myRng.Cells
        Res = Application.Match(myCell.Value, ListWks.Range("A:a"), 0)
        If IsError(Res) Then
            'not found
        Else
            With ListWks
                Set DestCell = .Cells(.Rows.Count, "B")..End(xlUp)
                If IsEmpty(DestCell.Value) Then
                    'keep it there
                Else
                    'move down a row
                    Set DestCell = DestCell.Offset(1, 0)
                End If
            End With

            DestCell.Value = myRng(Res).Offset(0, 3).Value
        End If
    Next myCell
End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)








--

Dave Peterson– Skjul sitert tekst –

– Vis sitert tekst –
Click to expand...

Thanks a lot Dave - I will try this out
Best regards Snoopy
 
Back
Top