Insert dynamic formula via VBA based on cell criteria

Question

I am currently working on something in Excel, and I need certain formulas to be in certain cells, but I don't want to just auto fill the entire column for obvious reasons. I have the following VBA code which correctly inserts the formula if the appropriate field (H) equals no.

My problem is, I cannot seem to find a way to make the formulas update based off of the row number. Only instances where "8" is used, does the row number need to be dynamic and update based off the row, the rest of the options are static. Any help would be greatly appreciated.

Here is the current code - I will continue to experiment while I try to find a way to do it.

    Dim x As Long
For x = 1 To 3008
    If InStr(1, Sheet1.Range("$H$" & x), "No") <> No Then
        Sheet1.Range("$K$" & x) = "=IF($C8="""",IF($B8="""","""",WORKDAY($B8,3,Admin!$B$5:$B$28)),WORKDAY($C8,3,Admin!$B$5:$B$28))"
        Sheet1.Range("$L$" & x) = "=IF($H8=""No"",IF($B8="""","""",IF($C8="""",IF($E8=""Ulster Sterling"",IF($B8="""","""",WORKDAY($K8,10)),IF($E8=""Ulster Euro"",IF($B8="""","""",WORKDAY($K8,10)),WORKDAY($K8,15))),WORKDAY($K8,10))),"""")"
        Sheet1.Range("$M$" & x) = "=IF($H8=""No"",IF(OR($L8=Admin!$B$5,$L8=Admin!$B$6,$L8=Admin!$B$8,$L8=Admin!$B$9,$L8=Admin!$B$10,$L8=Admin!$B$11,$L8=Admin!$B$13,$L8=Admin!$B$14,$L8=Admin!$B$16,$L8=Admin!$B$17,$L8=Admin!$B$18,$L8=Admin!$B$19,$L8=Admin!$B$21,$L8=Admin!$B$22,$L8=Admin!$B$24,$L8=Admin!$B$25,$L8=Admin!$B$26,$L8=Admin!$B$27)=TRUE,WORKDAY($L8,-1),IF(OR($L8=Admin!$B$7,$L8=Admin!$B$12,$L8=Admin!$B$15,$L8=Admin!$B$20,$L8=Admin!$B$23,$L8=Admin!$B$28)=TRUE,WORKDAY($L8,-2),$L8)),"""")"
        Sheet1.Range("$N$" & x) = "=IF($H8=""No"",IF($B8="""","""",WORKDAY($M8,10)),"""")"
        Sheet1.Range("$O$" & x) = "=IF($H8=""No"",IF(OR($N8=Admin!$B$5,$N8=Admin!$B$6,$N8=Admin!$B$8,$N8=Admin!$B$9,$N8=Admin!$B$10,$N8=Admin!$B$11,$N8=Admin!$B$13,$N8=Admin!$B$14,$N8=Admin!$B$16,$N8=Admin!$B$17,$N8=Admin!$B$18,$N8=Admin!$B$19,$N8=Admin!$B$21,$N8=Admin!$B$22,$N8=Admin!$B$24,$N8=Admin!$B$25,$N8=Admin!$B$26,$N8=Admin!$B$27)=TRUE,WORKDAY($N8,-1),IF(OR($N8=Admin!$B$7,$N8=Admin!$B$12,$N8=Admin!$B$15,$N8=Admin!$B$20,$N8=Admin!$B$23,$N8=Admin!$B$28)=TRUE,WORKDAY($N8,-2),$N8)),"""")"
        Sheet1.Range("$P$" & x) = "=IF($H8=""No"",IF($B8="""","""",WORKDAY($B8,38)),"""")"
        Sheet1.Range("$Q$" & x) = "=IF($H8=""No"",IF(OR($P8=Admin!$B$5,$P8=Admin!$B$6,$P8=Admin!$B$8,$P8=Admin!$B$9,$P8=Admin!$B$10,$P8=Admin!$B$11,$P8=Admin!$B$13,$P8=Admin!$B$14,$P8=Admin!$B$16,$P8=Admin!$B$17,$P8=Admin!$B$18,$P8=Admin!$B$19,$P8=Admin!$B$21,$P8=Admin!$B$22,$P8=Admin!$B$24,$P8=Admin!$B$25,$P8=Admin!$B$26,$P8=Admin!$B$27)=TRUE,WORKDAY($P8,-1),IF(OR($P8=Admin!$B$7,$P8=Admin!$B$12,$P8=Admin!$B$15,$P8=Admin!$B$20,$P8=Admin!$B$23,$P8=Admin!$B$28)=TRUE,WORKDAY($P8,-2),$P8)),"""")"
    End If
Next

you can do it without `For x = 1 To 65536` loop: `Sheet1.Range("K1:K100").Formula = "=IF(...)"` and excel automatically adjust all your formulas: in `K1` it would be `=IF($C8=`, in `K2` - `=IF($C9=` and so on. Btw do you really want to loop until 65536? this link may be interesing: [How to determine last used row/column](http://stackoverflow.com/questions/11169445/error-finding-last-used-cell-in-vba/11169920#11169920) — Dmitry Pavliv, Apr 05 '14 at 14:51
If I do it that way, it is putting the formulas in every single cell in column of the range referred to, I need it to only put the formulas in if it states "No" in the H column for that row. I agree that looping until 65536 is a bad idea and will adjust that to only include the data range I am using. — Merovin, Apr 05 '14 at 15:07
two ways for you: 1) `Sheet1.Range("K1:K100").Formula = "=IF(...)"` and then loop and remove formulas from cells where "No" in the H column. 2) do what you doing now and adjust formulas like this: `Sheet1.Range("K" & x) = "=IF($C" & x+7 & "=""""...)` — Dmitry Pavliv, Apr 05 '14 at 15:09

score 0 · Answer 1 · answered Sep 27 '15 at 02:26

For the sake of an answer, from a Comment by @simoco:

two ways for you:

1) Sheet1.Range("K1:K100").Formula = "=IF(...)" and then loop and remove formulas from cells where "No" in the H column.

2) do what you doing now and adjust formulas like this: Sheet1.Range("K" & x) = "=IF($C" & x+7 & "=""""...)

Insert dynamic formula via VBA based on cell criteria

1 Answers1