VBA - Change Formula dynamically

Question

I have an drop-down validating formuala for a cell and am using the generic formula inside the validation in vba.

Now, I want to automate it using the formula1 part inside the with loop here.

Here goes the Code,

Lastrow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Lastrow2 = Sheets("Config").Cells(Rows.Count, "R").End(xlUp).Row
    For i = 2 To Lastrow
        With Range("M" & i).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
        Operator:=xlBetween, Formula1:="=Config!R2:R10"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
        End With
    Next

how would you replace the part in the code - Formula1:="=Config!R2:R10" with the 10 being Lastrow2.

Thanks

@Xire or Sid29, please post as an answer so this post can be marked as complete so it does not sit in the open-state indefinitely. — Cyril
– Cyril, Commented Aug 31, 2017 at 13:42
Xire's answer worked. So, if Xire can put that in the answer section, I will mark it as accepted :) — Student of the Digital World
– Student of the Digital World, Commented Aug 31, 2017 at 13:43

Xire · Accepted Answer · 2017-08-31 13:52:40Z

1

The solution :

Formula1:="=Config!R2:R"&Lastrow2

answered Aug 31, 2017 at 13:52

Xire

1668 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA - Change Formula dynamically

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related