Excel Macro

A

aug99a

How could I accomplish the below scenerio:

I have three columns in the same worksheet:

Column A, Column B, and Column C.

Column A and B contain 15 didget numbers and Column C contains 7 didget
number.

Column A contains certain number, and Column B contains certain
number.
Most of the numbers in Column A & B match.

I want to compare Column's B & A and if the numbers don't match, I want
to shift cell in column B and Cdown till it finds a match in Column A so
that Column B matches Column A and also at the same time move the
corresponding value in Column C down with the Column B that is being
moved down.

I would like all of this to be done automatically. using Excel Macro

Thanks
 
D

Dave Peterson

Add a header to row 1 and try this macro that I've saved this from a few
previous posts:

Option Explicit
Sub testme()

Application.ScreenUpdating = False

Dim wks As Worksheet
Dim ColA As Range
Dim ColB As Range
Dim iRow As Long
Dim myCols As Long

Set wks = Worksheets("sheet1")
wks.DisplayPageBreaks = False
With wks
'row 1 has headers!
Set ColA = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
Set ColB = .Range("b2", .Cells(.Rows.Count, "B").End(xlUp))

With ColA
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

'change the mycols to the number of columns that
'are associated with column B

myCols = 2 ' columns B:C only
With ColB.Resize(, myCols)
.Sort key1:=.Cells(1), order1:=xlAscending, header:=xlNo
End With

iRow = 2
Do
If Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 0 Then
Exit Do
End If

If .Cells(iRow, "A").Value = .Cells(iRow, "B").Value _
Or Application.CountA(.Cells(iRow, "A").Resize(1, 2)) = 1 Then
'do nothing
Else
If .Cells(iRow, "A").Value > .Cells(iRow, "B").Value Then
.Cells(iRow, "A").Insert shift:=xlDown
Else
.Cells(iRow, "B").Resize(1, myCols).Insert shift:=xlDown
End If
End If
iRow = iRow + 1
Loop
End With

Application.ScreenUpdating = True

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top