From: Jazz on 6 Apr 2010 12:06 Definitely better and more functional than what I came up with. Thank you for your help! I appreciate it very much. "Project Mangler" wrote: > 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 > > > > > "Jazz" wrote in message > news:FDD4FD07-6F58-46D1-B983-9D9ACB02EC93(a)microsoft.com... > > 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. > > > > > . > From: Jazz on 6 Apr 2010 12:09 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. "Dave Peterson" wrote: > 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 wrote: > > > > 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. > > -- > > Dave Peterson > . > First  |  Prev  |