Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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 ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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 ... 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. . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formula even cells with data below it
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert Formula in cells from VBA | Excel Programming | |||
insert URL and data into cells | Excel Discussion (Misc queries) | |||
Help with the formula to insert cells | Excel Worksheet Functions | |||
Insert Formula and Copy to other cells | Excel Discussion (Misc queries) | |||
How to insert formula to a range of cells from VBA? | Excel Programming |