Printable View
Hi, all. I need help with following problem.
pls open the attachment, you will see a simple sample worksheet which shows the same contents but different format seperated by column i . I need a loop could help me check the whether the year and quarter are same, if they match, then copy the whole column's value to the other side.
Greatly appreciate any help! Thank you.
Can you re-post that with expected results? I don't quite understand what you stated.
If I understand your logic, Column D (FY2010/Q4) data should be copied to M (4Q2010), and E to N.
[vba]Option Explicit
Sub CopyColumnWithMatchingYearQuarter()
'http://www.vbaexpress.com/forum/showthread.php?p=219029&mode=linear#post219029
Dim wb As Workbook
Dim ws As Worksheet
Dim aCell As Range
Dim FirstAddress As String
Dim LastRow As Long
Dim C As Range
Dim A As Long
'Cycle through each Heading in Range.
For Each aCell In Sheets(1).Range("J4:N4")
'Using each one of the range,
'search for matching year.
With Worksheets(1).Range("D4:H4")
Set C = .Find(Right(aCell, 4), LookIn:=xlValues)
If Not C Is Nothing Then
'Found match, now compare the quarter.
FirstAddress = C.Address
Do
If C.Offset(1, 0) = StrReverse(Left(aCell, 2)) Then
'At the point, the Year and Quarter match,
'copy column contents down.
'Last Row in found range.
LastRow = Worksheets(1).Cells(65536, C.Column).End(xlUp).Row
'Copy Column
With Worksheets(1)
.Range(.Cells(aCell.Row + 2, aCell.Column), aCell(LastRow - 3, 1)) = _
.Range(.Cells(C.Row + 2, C.Column), .Cells(LastRow, C.Column)).Value
End With
End If
Set C = .FindNext(C)
Loop While Not C Is Nothing And C.Address <> FirstAddress
End If
End With
Next
End Sub[/vba]
hi,tinbendr. thank you for your great help. the macro could work, but there is one thing which i want to copy the cells address, so that when i change the number, no need to run the macro again, the copied number could automatically change. just like a formula, when i change the value, the result could also change. Is that possible??
Hi,xld. Sure. I have upload a new sample, pls have a check.
Can anyone please help?
As in a worksheet change event?
hi,Aussiebear. I am going to match the year and quarter and copy the ASP data to the right hand side. this sample workbook that i attached contains two worksheets, one is how it looks before running the macro, and the other one is after running a macro. So i need a macro to work it out. Could you please help? (And this is one more critical point is when i change one of the original number, then i no need to run the macro again, the copied number could automatically change. just like a formula, when i change the value, the result could also change. )Is that possible??
Greatly appreciated you kind help~