Excel VBA If范围.value =填满g列

Question

例如

if range

if A14 = 1 so fill G14 Ok如果A14 = 1，那么填充G14等等

例如如果范围A14:A200如果A14 = 1，那么填充G14如果A15 = 1，那么填充G15等等

Answer 1

你可以使用excel公式:

Sub IFSomething()
With Range("A14:A200") reference the needed range
With .Offset(, 6) ' reference the cells 6 columns to the right of referenced range
.FormulaR1C1 = "=IF(RC[-6]=1,""OK"","""")" ' place a formula in referenced range
.Value = .Value ' leave only values
End With
End With

End Sub

Answer 2

所以这里是修改解决方案，我希望这解决你的问题。

Sub If_loop_test()
Dim x As Integer

For x = 1 To 200

If Range("A" & x).Value = 1 Then
Range("G" & x).Value = "ok"
End If
Next

End Sub

Answer 3

这是一个相对干净和通用的版本。
请记住，如果您要将此应用于大型数据集，这可能会很慢。您可以通过将范围导入数组并遍历该数组来修复此问题。你的代码将从在非常大的数据集上花费10秒减少到不到一秒。

Option Explicit
Sub If_Offset_Value()

Dim WS As Worksheet
Dim RG As Range
Dim CL As Range
Dim CheckVal As Variant

' > Change this to whatever value you're checking for.
CheckVal = 1

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

Set WS = ThisWorkbook.Worksheets("My WorkSheet Name")
Set RG = WS.Range("A14:A200")

For Each CL In RG.Cells
If CL.Value = CheckVal Then

' > Couple of options here depending on your needs:
'   Both options give you the same result, but Offset
'   moves left and right if you change RG column,
'   whereas column letter referense will stay G

'1) Offset Method
CL.Offset(0, 6).Value = "OK"

'2) Reference Column Letter
WS.Range("G" & CL.Row).Value = "OK"

End If
Next CL

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub