Using a formula within a Macro but with varying row amounts

[neal_dodd]

Hi everyone

Hoping someone can help me, please!

I have a standard report that I produce, which is a tidied up version of raw data that is sent to me. I therefore have a Macro that I can apply that gets me a long way down the line for all the things that I would otherwise do for every report.

The reports vary in their number of rows - anything from 50 to 9,000.

There are two things that I do that I have not been able to include in the Macro because they they involve formulas that need to be copied to the bottom of the report. So if I try to include them, all that happens in the Macro is that the formula is copied to the exact number of rows that I happened to have when I recorded the Macro.

Is there a way of telling the Macro that when I copy the formula down, it should go as far as the last row of data?

Thanks a lot and let me know if you need more info!

Neal

[Pepe Le Mokko]

Administrative note

Still using XL2003 as indicated in your profile?
If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
Thanks

[sweep]

Hi,

You can identify the last row that contains data like this (Column A for example)

Please Login or Register  to view this content.

You could assign that value to a variable, like this..

Please Login or Register  to view this content.

and then use it to define the range that you want the macro to work on like this

Please Login or Register  to view this content.

I hope that gets you started..

[neal_dodd]

Sorry, didn't realise.. it's Microsoft Office 365 Business, I'll update it now!

[neal_dodd]

Hi,

You can identify the last row that contains data like this (Column A for example)

Please Login or Register  to view this content.

You could assign that value to a variable, like this..

Please Login or Register  to view this content.

and then use it to define the range that you want the macro to work on like this

Please Login or Register  to view this content.

I hope that gets you started..

Thank you, and I'm sorry for my limited knowledge, but I don't really know where I put those formulas and at what stage?

I think I understand what you've written but am unsure how to use it in the process.

Thanks a lot for your time

[sweep]

You'll have to amend your macro with this code - or something similar to it. Can you post the macro that you have recorded? To view the VBA editor you can press ALT&F11.

[neal_dodd]

Thanks - here's what came up.

I have just recorded the Macro by starting to record and then running through the tasks, so don't usually use this screen!


Sub Rankingreport()
'
' Rankingreport Macro
'

'
ActiveCell.Offset(0, -6).Columns("A:E").EntireColumn.Select
Selection.ColumnWidth = 18.43
Selection.ColumnWidth = 15.86
ActiveCell.Select
ActiveCell.FormulaR1C1 = "Postal sector"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Target HH"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Total HH"
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "Penetration"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "=RC[-2]/RC[-1]"
ActiveCell.Columns("A:A").EntireColumn.Select
Selection.NumberFormat = "0.00%"
ActiveCell.Offset(1, 1).Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.NumberFormat = "0.00"
Selection.NumberFormat = "0.0"
Selection.NumberFormat = "0"
ActiveCell.Offset(-1, 0).Rows("1:1").EntireRow.Select
Selection.Font.Bold = True
ActiveCell.Offset(6, 3).Range("A1").Select
End Sub

[sweep]

OK,

First order of business - can you take a look at adding code tags to your post so it formats correctly?

Next, your macro seems to do the following:

The column width is set to 15.43 for A:E
In row 1, add headers to columns A through D
In column D, the formula of =B1/C1 is inserted
Column D is set to percentage format
Row 1 is made bold

I guess that you want the formula copied down the length of the data?

[Pepe Le Mokko]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)