vba code to update data as per criteria

Example.xlsxHi,

i am trying for vba code for below criteria,

1. in sheet1 i am filtering for column D for dates i want and copy mail id of column H
2. goto sheet 2 and find that mail id in column D, now according to the column C in sheet1 confirmed and cancelled i would like to enter the registration as yes if confirmed
    and for cancelled as cancelled in sheet2
3.update the offering date(column F-sheet2) as event start date(column B-sheet1) for yes
4.update comments only for cancelled as "Cancelled on 10/04/2018(column E date-sheet1) from the 16/04/2018(column B-sheet1) offering"

Please suggest.

Thank you in advance.
rdy123 rdyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsExcel & VBA ExpertCommented:
Hi,

Why you need VBA to perform such simple update?

If you copy Event Title Column from Sheet1 to Programme Title Column in Sheet2, rest can be updated with formula. Check in attached...
rdy123rdy_Update-Sheet2.xlsx
0
rdy123 rdyAuthor Commented:
Thank you Shums.

As the file always gets updated, i would like to do it in VBA.

i added Browse option in vba code inorder to use updated version always.
0
ShumsExcel & VBA ExpertCommented:
I understand, but did you check the formulated version file I posted?

Whenever Sheet1 is updated, you just need to copy Event Title from Sheet1 and paste it to Programme Name in Sheet2, then drag down the formula as long you need.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

ShumsExcel & VBA ExpertCommented:
If your concern is just to update the Remarks Column, then you can just add below formula in Column I, Title it with Remarks

=IF($A2="","",IF(VLOOKUP($A2,$A:$H,3,0)="Confirmed","","Cancelled on "&TEXT(VLOOKUP($A2,$A:$H,5,0),"dd/mm/yyyy")&" from the "&TEXT(VLOOKUP($A2,$A:$H,2,0),"dd/mm/yyyy")&" offering"))

Check in attached...
rdy123rdy_Update-Sheet2_v2.xlsx
0
rdy123 rdyAuthor Commented:
Actually there are 2 different worksheets, sheet1 is one worksheet and sheet2 is one worksheet for example i gave in 1 worksheet with 2 sheet tabs.
i have 4 different files similar way, so thought it would be good if i give it in vba.

i will try if these formulas can be added in vba.

Thanks again.
0
ShumsExcel & VBA ExpertCommented:
Try below:
Which will update Sheet1 with Remarks Column added and will highlight the cells with yellow color if it has "Cancelled"
Sub UpdateRemarks()
Dim Ws As Worksheet
Dim LRow As Long
Dim ColRng As Range
Set Ws = Worksheets("Sheet1")
LRow = Ws.Range("A" & Rows.Count).End(xlUp).Row
Set ColRng = Ws.Range("I2:I" & LRow)
Application.ScreenUpdating = False
Ws.Range("I1").Value = "Remarks"
ColRng.FormulaR1C1 = "=IF(RC1="""","""",IF(VLOOKUP(RC1,C1:C8,3,0)=""Confirmed"","""",""Cancelled on ""&TEXT(VLOOKUP(RC1,C1:C8,5,0),""dd/mm/yyyy"")&"" from the ""&TEXT(VLOOKUP(RC1,C1:C8,2,0),""dd/mm/yyyy"")&"" offering""))"
ColRng.Value = ColRng.Value
With Ws
    .Cells.FormatConditions.Delete
    With ColRng
        .FormatConditions.Add Type:=xlExpression, Formula1:="=ISNUMBER(SEARCH(""Cancelled"",RC))=TRUE"
        .FormatConditions(.FormatConditions.Count).SetFirstPriority
        With .FormatConditions(1).Interior
            .PatternColorIndex = xlAutomatic
            .Color = 65535
            .TintAndShade = 0
        End With
        .FormatConditions(1).StopIfTrue = False
        .Columns.AutoFit
    End With
End With
Application.ScreenUpdating = True

End Sub

Open in new window

In attached run macro "Update Remarks"
rdy123rdy_Update-Sheet2_v3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rdy123 rdyAuthor Commented:
Thank you Shums, will check and get back to you :)
0
ShumsExcel & VBA ExpertCommented:
No further comments been added. :(
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.