Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
I have the formula below and have tried copying it down column in macro. It
will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Hi,
Maybe this which will fill down as far as there are data in column O Range("Q2").Select ActiveCell.FormulaR1C1 = "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Lastrow = Cells(Cells.Rows.Count, "O").End(xlUp).Row Range("Q2:Q" & Lastrow).FillDown -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: I have the formula below and have tried copying it down column in macro. It will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Hi Mike,
Thanks for the reply. It worked on filling down the column, but the formula still isn't calculating properly. It fills in the same amount all the way down which is the calculated amount for Q2. If I click on Q3 and then click in the function in the function bar, it will highlight with different colored boxes the cells in the equation. When I click the check mark to the left of formula, it calculates that cell with the correct answer. What am I doing wrong? I'm very much a novice with vba, but am very impressed with how much automation can be accomplished with macros. Again, any help is greatly appreciated. Thanks, Pam "Mike H" wrote: Hi, Maybe this which will fill down as far as there are data in column O Range("Q2").Select ActiveCell.FormulaR1C1 = "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Lastrow = Cells(Cells.Rows.Count, "O").End(xlUp).Row Range("Q2:Q" & Lastrow).FillDown -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: I have the formula below and have tried copying it down column in macro. It will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Do you have calculation mode set to Automatic?
Gord Dibben MS Excel MVP On Wed, 2 Jun 2010 07:58:06 -0700, PHisaw wrote: Hi Mike, Thanks for the reply. It worked on filling down the column, but the formula still isn't calculating properly. It fills in the same amount all the way down which is the calculated amount for Q2. If I click on Q3 and then click in the function in the function bar, it will highlight with different colored boxes the cells in the equation. When I click the check mark to the left of formula, it calculates that cell with the correct answer. What am I doing wrong? I'm very much a novice with vba, but am very impressed with how much automation can be accomplished with macros. Again, any help is greatly appreciated. Thanks, Pam "Mike H" wrote: Hi, Maybe this which will fill down as far as there are data in column O Range("Q2").Select ActiveCell.FormulaR1C1 = "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Lastrow = Cells(Cells.Rows.Count, "O").End(xlUp).Row Range("Q2:Q" & Lastrow).FillDown -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: I have the formula below and have tried copying it down column in macro. It will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Hi,
It sounds like calculation is set to manual E2003 Tools|Options|calculation tab and select automatic E2007 Formulas tab|Calculation options and select automatic -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: Hi Mike, Thanks for the reply. It worked on filling down the column, but the formula still isn't calculating properly. It fills in the same amount all the way down which is the calculated amount for Q2. If I click on Q3 and then click in the function in the function bar, it will highlight with different colored boxes the cells in the equation. When I click the check mark to the left of formula, it calculates that cell with the correct answer. What am I doing wrong? I'm very much a novice with vba, but am very impressed with how much automation can be accomplished with macros. Again, any help is greatly appreciated. Thanks, Pam "Mike H" wrote: Hi, Maybe this which will fill down as far as there are data in column O Range("Q2").Select ActiveCell.FormulaR1C1 = "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Lastrow = Cells(Cells.Rows.Count, "O").End(xlUp).Row Range("Q2:Q" & Lastrow).FillDown -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: I have the formula below and have tried copying it down column in macro. It will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Mike and Gord,
My spreadsheet is set to Automatic via ToolsCalc tab, but I also found this line of code in searching while hopefully waiting for a reply and it works. Application.Calculation = xlCalculationAutomatic My code is very pieced together and I'm sure it is bloated with a lot of unnecessary extras as some was done from recorded macros, but it seems to work except for two issues below that I'm hoping you will help with. I need to sort my columns on month-year. When I try to copy the date to this format in another column, it shows the format, but also has the day in the date. Example: InvoicedDate Invoiced Month Invoiced Month Actual Date formatted mm-yy 1/27/09 Jan-09 1/27/09 Is there a way that I can use Invoiced Date and sort by month regardless of day. I have another column (Class) that I need to sort on and need all monthly entries to be grouped so I can get all classes sorted alpha in each month. Also, using part of Ron deBruin's code for deleting rows based on data in cells, how can I search for two entries ("*09" and "*10") without having to copy all the code again for second search? Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value Like "*10" Then .EntireRow.Delete End If End With Next Lrow I really appreciate your time and help. Thanks again, Pam "Gord Dibben" wrote: Do you have calculation mode set to Automatic? Gord Dibben MS Excel MVP On Wed, 2 Jun 2010 07:58:06 -0700, PHisaw wrote: Hi Mike, Thanks for the reply. It worked on filling down the column, but the formula still isn't calculating properly. It fills in the same amount all the way down which is the calculated amount for Q2. If I click on Q3 and then click in the function in the function bar, it will highlight with different colored boxes the cells in the equation. When I click the check mark to the left of formula, it calculates that cell with the correct answer. What am I doing wrong? I'm very much a novice with vba, but am very impressed with how much automation can be accomplished with macros. Again, any help is greatly appreciated. Thanks, Pam "Mike H" wrote: Hi, Maybe this which will fill down as far as there are data in column O Range("Q2").Select ActiveCell.FormulaR1C1 = "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Lastrow = Cells(Cells.Rows.Count, "O").End(xlUp).Row Range("Q2:Q" & Lastrow).FillDown -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while still sufficiently answering the question. "PHisaw" wrote: I have the formula below and have tried copying it down column in macro. It will calculate the first cell "Q2" properly, but copies the same calculation from Q2 to all other cells. If I click in the formula and then the check mark to the left of formula, it calculates properly in the cell. How can I make it calculate properly from the macro? Also the range will change with each import to the worksheet the macro is applied to. How can I write the macro to apply to all cells in column Q where there is data in other columns in formula? Range("Q2").Select ActiveCell.FormulaR1C1 = _ "=IF(OR(RC15={30,0}),RC7,IF(AND(rc15=""cc""),IF(OR (RC3={""mdu"",""kiu"",""cvu""}),RC7+90,RC7+30)))" Range("Q2:Q78").FillDown Thanks in advance for any help available. Pam . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
Here is the part about "*09" and "*10". This will delete rows with *09 or
*10 dates. Sub DeleteRows() With ActiveSheet Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row End With For Lrow = Lastrow To Firstrow Step -1 If Not IsError(Cells(Lrow, 1).Value) Then If Cells(Lrow, 1).Value Like "*10" Or _ Cells(Lrow, 1).Value Like "*09" Then ActiveSheet.Cells(Lrow, 1).EntireRow.Delete End If End If Next Lrow End Sub HTH, -- Data Hog "PHisaw" wrote: Also, using part of Ron deBruin's code for deleting rows based on data in cells, how can I search for two entries ("*09" and "*10") without having to copy all the code again for second search? Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value Like "*10" Then .EntireRow.Delete End If End With Next Lrow I really appreciate your time and help. Thanks again, Pam |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula Not Working Properly
J_Knowles,
Thanks so much for your help. It worked just as needed. Pam "J_Knowles" wrote: Here is the part about "*09" and "*10". This will delete rows with *09 or *10 dates. Sub DeleteRows() With ActiveSheet Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row End With For Lrow = Lastrow To Firstrow Step -1 If Not IsError(Cells(Lrow, 1).Value) Then If Cells(Lrow, 1).Value Like "*10" Or _ Cells(Lrow, 1).Value Like "*09" Then ActiveSheet.Cells(Lrow, 1).EntireRow.Delete End If End If Next Lrow End Sub HTH, -- Data Hog "PHisaw" wrote: Also, using part of Ron deBruin's code for deleting rows based on data in cells, how can I search for two entries ("*09" and "*10") without having to copy all the code again for second search? Firstrow = .UsedRange.Cells(1).Row Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row For Lrow = Lastrow To Firstrow Step -1 With .Cells(Lrow, "A") If Not IsError(.Value) Then If .Value Like "*10" Then .EntireRow.Delete End If End With Next Lrow I really appreciate your time and help. Thanks again, Pam |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula and worksheet will not working properly | Excel Worksheet Functions | |||
formula not working properly | Excel Worksheet Functions | |||
Yes No box no working properly | Excel Programming | |||
DirectDependents not working properly. | Excel Programming | |||
But not working properly | Excel Discussion (Misc queries) |