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:FDD4FD076F5846D1B9839D9ACB02EC93(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 ctrlenter 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 