Formula and validation applied if target cell contains value - VBA

[uday]

Hi,

I have a productivity tracker wherein lot of line items needs to be populated with formula if cell contains value. I want to avoid simple IF condition formula as because the data is huge and if I am applying forumula to all related rows then the file size will be huge.

Please see attached file for your reference. Column C has the item list and Column A and B have Date & Vlookup formula. Also Column D has validation. This needs to be executed from vba with worksheet function. The validation also applied in Column D if the Item column range has data.

Regards,
Uday

 

[p45cal]

try something alongthe lines of:

Sub blah()
Dim myRng As Range
With ActiveSheet
  On Error Resume Next
  Set myRng = Intersect(.UsedRange, .Columns(3)).Offset(1).SpecialCells(xlCellTypeConstants, 23)
  On Error GoTo 0
  If Not myRng Is Nothing Then
    For Each are In myRng.Areas
      are.Offset(, -2).FormulaR1C1 = "=TODAY()"
      are.Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[1],'Lookup Sheet'!C[-1]:C,2,0)"
      With are.Offset(, 1).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Validation!$A$1:$A$3"
        .IgnoreBlank = True
        .InCellDropdown = True
        .ShowInput = True
        .ShowError = True
      End With
    Next are
  End If
End With
End Sub

 

[uday]

Thanks for your response.

Can you please tell me why you have put "are" before declaring the formula.
i.e., are.Offset(, -2).FormulaR1C1.

Also, I am not able to call this macro for worksheet change event.

Regards,
Uday

 

[p45cal]

Can you please tell me why you have put "are" before declaring the formula.
i.e., are.Offset(, -2).FormulaR1C1.

are is merely my choice of variable name for each area (a contiguous range of cells):
For Each are In myRng.Areas
it could just as well have been:
For Each zzz In myRng.Areas
when I would have used:
zzz.Offset(, -2).FormulaR1C1

Also, I am not able to call this macro for worksheet change event.

Try this in the sheet concerned's code-module:

Private Sub Worksheet_Change(ByVal Target As Range)
Set myRng = Intersect(Columns(3), Target)
If Not myRng Is Nothing Then
  Application.EnableEvents = False
  For Each cll In myRng.Cells
    With cll
      If Len(.Value) > 0 Then
        .Offset(, -2).FormulaR1C1 = "=TODAY()"
        .Offset(, -1).FormulaR1C1 = "=VLOOKUP(RC[1],'Lookup Sheet'!C[-1]:C,2,0)"
        With .Offset(, 1).Validation
          .Delete
          .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=Validation!$A$1:$A$3"
          .IgnoreBlank = True
          .InCellDropdown = True
          .ShowInput = True
          .ShowError = True
        End With
      Else
        .Offset(, -2).Resize(, 2).ClearContents
        .Offset(, 1).Validation.Delete
        .Offset(, 1).ClearContents
      End If
    End With
  Next cll
  Application.EnableEvents = True
End If
End Sub

 

[uday]

Thanks!

Now its working fine. However the Today date is not fixed as usual. The nature of the formula itself provide today's date. I want to fixed the date according to their data entry. i.e., If any data entered in previous day it should remain same.

Regards,
Roy

 

[p45cal]

Change:
.Offset(, -2).FormulaR1C1 = "=TODAY()"
to:
.Offset(, -2).value=date

 

[uday]

Change:
.Offset(, -2).FormulaR1C1 = "=TODAY()"
to:
.Offset(, -2).value=date

Thanks working like a charm

 

[uday]

Two questions :

1) Do we always need to declare variable? In this code you have not declared cll with dimension.

2) Can you please explain application true and false reasonability?

 

[p45cal]

1) Do we always need to declare variable? In this code you have not declared cll with dimension.

Clearly not. If you have Option Explicit at the top of the module you would need to Dimension all variables. In theis case, cll is a Range.

2) Can you please explain application true and false reasonability?

Application.EnableEvents= True/False switches on/off the ability of event handlers to respond; Worksheet_Change is an event handler which responds to changes in the sheet. Since the code is also changing the sheet that would trigger the event handler again. EnableEvents stops this.

 

[uday]

Thanks

 

[uday]

Can I select validation items (as, ds , qw) by default after applying the validation?