PDA

View Full Version : Sleeper: A Matching Problem



Dreamer
07-04-2005, 09:49 PM
Hi,

Here's my problem...

Col A is the serial no.(if any), Col B is the description, Col C is month

What I need to do is... as we want to update the info to our clients ASAP, we will normally put all new data at the bottom of the spreadwheet first(without assigning any serial no.) ..and afterwards, we will do the grouping...

For example:


col --A--B ----------C
ABC111 Soft Drinks (empty)
.........
.........

in row X, find.....

col --A--B ----------C
(empty)Soft Drinks May

After grouping, it becomes...
col A -- B---- C
ABC111 Drinks May

In other words, update the new info "May" to existing record..
Since there are over 10000 rows which need to proceed, does a marco
can help this? Thanks in advance! ^^

Bob Phillips
07-05-2005, 01:45 AM
Sub UpdateData()
Dim iLastRow As Long
Dim iLastData As Long
Dim iRow As Long
Dim i As Long
Dim rng As Range
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("B1").Resize(iLastRow)
iLastData = Cells(Rows.Count, "B").End(xlUp).Row
For i = iLastRow + 1 To iLastData
iRow = 0
On Error Resume Next
iRow = Application.Match(Cells(i, "B").Value, rng, 0)
On Error GoTo 0
If iRow > 0 Then
Cells(iRow, "C").Value = Cells(i, "C").Value
End If
Next i
Range("B" & iLastRow + 1).Resize(iLastData - iLastRow + 1, 2).ClearContents
End Sub

Dreamer
07-05-2005, 08:36 AM
Thanks! It really help !

Forgive me to ask one more point... If I want to hightlight.. for example 100 rows, and run the marco for the selected area, how should I edit these codes?:)