If statement with a loop - large data set

Question

Currently I am using the following code to add a formula to cells in the column for a predefined range of cells. The problem is that the number of cells I need the formula in fluctuates based on how big the data set is.

Range("R9").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]<0,""False"",""True"")"
Selection.AutoFill Destination:=Range("R9:R12000")
Range("R9:R62053").Select

What I want to do is for every cell that has a value in say column B, I want the macro to insert the formula in the corresponding cell in column C, and stop once it reaches a point where the cell in column b has no value.

Possible duplicate of [EXCEL-VBA better way to find last row](https://stackoverflow.com/questions/38882321/excel-vba-better-way-to-find-last-row) — cybernetic.nomad, Jul 09 '18 at 16:47

score 0 · Answer 1 · answered Jul 09 '18 at 16:54

0

The code below is based off the OP's comments. Where as, his code seems to be targeting R9:R12000"

Dim cell As Range, Target As Range

With Worksheets("Sheet1")
    Set Target = .Range("B9", .Range("B" & .Rows.Count).End(xlUp))
    For Each cell In Target
        If cell.Value <> "" Then cell.Offset(0, -1).Formula = "=IF(RC[-1]<0,""False"",""True"")"
    Next
End With

answered Jul 09 '18 at 16:54

TinMan

6,624
2
10
20

Having some difficulty with this code. The code changes the values in cell a and references Cell xfd in formula. If I change the following code "Set Target = .Range("B9", .Range("B" & .Rows.Count).End(xlUp))" to reference cell "D" instead of "B" then the code works. Formula is inserted in column B with ref to cell A. Can you explain why this is happening please? – Jav171 Jul 11 '18 at 19:03

If statement with a loop - large data set

1 Answers1