Prev: Formula to find text and count it within an entire workbook
Next: for loops
From: Jazz on 5 Apr 2010 14:56
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: ker_01 on 5 Apr 2010 15:14
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

"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.
>
From: Project Mangler on 5 Apr 2010 15:42
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: Dave Peterson on 5 Apr 2010 15:52
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
From: Jazz on 6 Apr 2010 12:05
Thank you ker_01. I will think a lot about your points and keep them in mind
if I post another question.

"ker_01" wrote:

> 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
>
> "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.
> >
 |  Next  |  Last
Pages: 1 2
Prev: Formula to find text and count it within an entire workbook
Next: for loops