Having a Problem Using Autofill within a MACRO.

[Zac]

Hope I can explain this so someone understands it.

I am trying to debug a MACRO created for a sales commission report.
In COL O is the Comm Rate.
NOTE: The Range in this Column varies from month to month. Last month when I initially recorded the macro this col had 178 rows. This month it's 228. With that in mind here's my dilemma:

The Comm Rate is listed as x.xxx.
A 1% rate is listed as 1.000.
A 1/2% rate is listed as 0.500.
I need to get it to read 0.010, .005, etc.
so I recorded the macro in P2 to read: O2*.01
Leaving the result as .010.
In my initial Macro I sent to the corner of P2 cell and dbl click to autofill to the end of the active spreadsheet.
Then I COPY the entire Col and Paste Values into COL "O" to correct all the data to the correct %. I then delete COL "P".

My problem. The Initial MACRO still wants to use O2:O178 as the AUTOFILL instead of O2:O228. Why?? How can I sneak around this problem?

Is there a way to reformat COL"O" so the data changes from 1.000 to .010? Tried everything I could think of. I thought the Autofill would change from month to month.

Desperately need someones help.
If you wish to call me use 800-536-3900 x154
ask for Mike.
If you need more information please ask.

Zac

 

[Jay Petrulis]

Rewrite your code. Delete all the adjustments made to the worksheet on the "P" column. Make all the adjustments to the "O" column directly.

Try something like:
----------------
Sub test()
Dim UsedRng As Range, UsedCell As Range

With ActiveSheet
Set UsedRng = Intersect(.UsedRange, .Range("O2:O65536"))
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell
End With

End Sub
---------------

If this doesn't work, please post your code.

Bye,
Jay

 

[Zac]

Thanks Jay. I have never used the
DIM coding so I hope I get it right.

Zac

 

[Zac]

Jay Petrulis
Need your help.

Copied over some of the data from COL "O
to R25:R35.

Then I recorded a short macro to see if it works (as follows):
'Application.Goto Reference:="R25C17"
With ActiveSheet
Set UsedRng = Intersect(.UsedRange, .Range("R25:R65536"))
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell
End With

End Sub



Getting a DEBUG msg at "SET USEDRNG......"

 

[Jay Petrulis]

Is there anything in column R? I cannot replicate your problem when there is data in the used range.

Sub test()
Dim UsedRng As Range, UsedCell As Range

Application.Goto Reference:="R25C17"
With ActiveSheet
Set UsedRng = Intersect(.UsedRange, .Range("R25:R65536"))
If Not UsedRng Is Nothing Then
For Each UsedCell In UsedRng
UsedCell = UsedCell * 0.01
Next UsedCell
End If
End With

End Sub

This will stop the error if there are no entries in col R, but that doesn't really solve your problem.

Please post your code.

Bye,
Jay

 

[Zac]

Jay,

I'm pretty sure I've got it figured out.
I will let you know

Thanks for your help.
Zac