CODEWITHSUNDEEP
vba
Chito
Chito

Reputation: 315

VBA to add excel formula by selecting the column name as range

I have a column name as "Validation" but column number keep changing. How can I find this column by the name and take that as range.

Currently below is the macro which I am using which checks the column M and adds the formula for all the cells if column M is not blank.

My new expectation is,

  1. See column M, if has cell value as "BLM" & "CFG" then add the excel formula by finding the column name "Validation" for those having that cell value as "BLM" & "CFG", skip if blank.
  2. Change all these formula to cell values
Sub test_macro()
    Dim sFormula As String
    Dim rng As Range
    Dim ws2 As Worksheet

    sFormula = "=IF(IFERROR(VLOOKUP(RC[-11],'Service ID Master List'!C[-11],1,0),""Fail"")=""Fail"",""Check SESE_ID"","""")&IF(IFERROR(VLOOKUP(RC[-9],Rules!C[-13],1,0),""Fail"")=""Fail"","" | Check SESE_RULE"","""")&IF(TRIM(RC[-5])="""","""",IF(IFERROR(VLOOKUP(RC[-5],Rules!C[-13],1,0),""Fail"")=""Fail"","" | Check SESE_RULE_ALT"",""""))&IF(RC[-7]=""TBD"","" | Check SEPY_ACCT_CAT"","""")"

    Set ws2 = ActiveSheet

    With ws2
        Set rng = .Range("M2")
        Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
    End With
    rng.SpecialCells(xlCellTypeConstants).Offset(0, 1).FormulaR1C1 = sFormula
    'changing formulas in values
    Columns("N:N").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N1").Select
    Application.CutCopyMode = False
End Sub

Upvotes: 0

Views: 2203

Answers (1)

L42
L42

Reputation: 19737

You can use Find Method to find your range.
Something like:

Edit1:

'~~> get the position of 'Validation' column and set rng variable
With ws2
    Dim valCol As Long
    valCol = .Rows("1:1").Find("Validation").Column '~~> change to suit
    Set rng = .Range("M2")
    Set rng = .Range(rng, .Cells(.Rows.Count, rng.Column).End(xlUp))
End With

Then check Column M entries:

Dim cel As Range
For Each cel In Rng
    If cel = "BLM" Or cel = "CFG" Then
        With ws2.Cells(cel.Row, valCol)
            .Formula = sFormula
            .Value = .Value
        End With
    End If
Next

This assumes that a column with Validation as name always exist and the formula is correct.
Also check this out to see ways of avoiding select which will greatly improve coding.

Upvotes: 0

Related Questions