Formula Not Working Properly

[PHisaw]

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

 

[Mike H]

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]

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

 

[Gord Dibben]

Do you have calculation mode set to Automatic?


Gord Dibben MS Excel MVP

 

[Mike H]

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]

Mike and Gord,

My spreadsheet is set to Automatic via Tools>Calc 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

 

[J_Knowles]

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,

 

[PHisaw]

J_Knowles,

Thanks so much for your help. It worked just as needed.

Pam