PDA

View Full Version : use if else loop



Yjmmay34
07-09-2010, 02:05 AM
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.

Bob Phillips
07-09-2010, 03:38 AM
Can you re-post that with expected results? I don't quite understand what you stated.

Tinbendr
07-11-2010, 02:53 AM
If I understand your logic, Column D (FY2010/Q4) data should be copied to M (4Q2010), and E to N.
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

Yjmmay34
07-12-2010, 07:24 PM
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??

Yjmmay34
07-12-2010, 07:54 PM
Hi,xld. Sure. I have upload a new sample, pls have a check.

Yjmmay34
07-14-2010, 10:28 PM
Can anyone please help?

Aussiebear
07-14-2010, 11:13 PM
As in a worksheet change event?

Yjmmay34
07-14-2010, 11:27 PM
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~