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?
 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
ShumsDistinguished Expert - 2017Commented:
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
 
ShumsDistinguished Expert - 2017Commented:
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
 
rdy123 rdyAuthor Commented:
Thank you Shums, will check and get back to you :)
0
 
ShumsDistinguished Expert - 2017Commented:
No further comments been added. :(
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.