Prev: Formula to find text and count it within an entire workbook
Next: for loops
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" <Jazz(a)discussions.microsoft.com> 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  | 
Pages: 1 2
Prev: Formula to find text and count it within an entire workbook
Next: for loops