Insert formula even cells with data below it

[Jazz]

Using this code

Sub Insertformula ()
For Check = 4 To 40000 Step 2
If Cells(Check, "b") <> "" Then Cells(Check, "b") =
"=IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)"
Next Check
End Sub

My objective is to put this formula

=IF(OR(I5="",J5=""),"",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)

Into every even cell in Column B with data below it. However if you tried
the macro you would see it does not work. If I could get any help thank you.

 

[ker_01]

Please tell us what does not work; "it does not work" doesn't tell us where
it is doing something different than what you expect. Is it erroring out? If
so, on what line? Does it run but not do what you thought it should?

Without the rest of your code, I have to also address some of my assumptions
here;

"Step 2" means that this will only check every other row of your
spreadsheet. If you want to check every row to add the formula, then just
delete the "Step 2"

"Cells(Check, "b")" doesn't have any context. I'd expect something like
Activeworksheet.Cells or Sheets(1).cells or Sheets("Dec 2003 data").Cells, or
a with statement at the top; "With Sheets(1)"... ".Cells"...

Personally, I use almost exclusively A1 format with .range, so I'm not sure
about the syntax for cells, but for range you can explicitly indicate a
formula (and format), e.g.:
Sheet1.Range("A1").Formula =
Sheet1.Range("A1").FormulaArray =
Sheet1.Range("A1").FormulaR1C1 =

HTH,
Keith

 

[Project Mangler]

Not sure as to whether the formula does what you need, but this should be
nearer to working:

Sub Insertformula()
For Check = 4 To 40000 Step 2
If Cells(Check - 1, 2) <> "" Then Cells(Check, 2) = "=IF(OR(I5=" & Chr(34) &
Chr(34) & ",J5 =" & Chr(34) & Chr(34) & ")," & Chr(34) & Chr(34) &
",NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)"
Next Check
End Sub

 

[Dave Peterson]

If you're going to fill a range with a formula that is essentially the same for
all the cells, you can use this technique when you do it manually:

Select the range to get the formula
Type the formula with respect to the activecell
hit ctrl-enter to fill the range with that (adjusted) formula.

Excel will adjust the formula -- just like if you had copied and pasted.

Instead of plopping the formula into almost 40000 cells, I used column A to
determine the last row to get the formula.

Option Explicit
Sub Insertformula()

Dim LastRow As Long

With ActiveSheet
'I used column A to find the last row
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

.Range("B4:B" & LastRow).Formula _
= "=IF(OR(I5="""",J5=""""),""""," _
& "NETWORKDAYS(I5,J5,Holidays!A$1:A$39)-1)"
End With

End Sub

The other thing that's important is to double up those doublequotes in the
formula.

 

[Jazz]

Thank you ker_01. I will think a lot about your points and keep them in mind
if I post another question.

 

[Jazz]

Definitely better and more functional than what I came up with. Thank you
for your help! I appreciate it very much.

 

[Jazz]

Wow Dave this code is phenomenal; thank you. I also appreciate your advice
for carrying out this procedure manually. Thank you very very much!

Ah ha! i knew I needed more quotes but I was putting them in the wrong
spots, thank you for clarifying that. Everything has been very helpful. I
am grateful for your help.