我有一个列名称为“验证”,但列号不断变化 . 如何通过名称找到此列并将其作为范围 .
目前下面是我正在使用的宏,它检查列M并添加所有单元格的公式,如果列M不为空白 .
我的新期望是,
-
参见列M,如果单元格值为"BLM"&"CFG",则通过查找具有该单元格值为"BLM"&"CFG"的列名称"Validation"来添加excel公式,如果为空白则跳过 .
-
将所有这些公式更改为单元格值
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
1 回答
您可以使用“查找方法”查找范围 .
就像是:
Edit1:
然后检查列M条目:
这假设始终存在名称为Validation的列,并且公式正确 .
也check this out to see ways of avoiding select which will greatly improve coding .