copying formula down indeterminate length clomn 1

[catbert]

I am new to VBA and currently editing recorded macros so my undestanding is limited. Please be gentle!

I receive a log file everyday where I want to insert a calculation at the end of each row, but I never know how many rows there will be. How can I achieve this without copying it into the whole column - I have tried this using an if statement to put a null string if there is no data, but using a named range or used.range to graph the data then includes all the 0 strings.

I have tried using relative references to go down the last column of data, offset and name the next cell as a range - but this code always specifies the row - is there a way around this:
ActiveWorkbook.Names.Add Name:="lastcell", RefersToR1C1:="=Log!R95C6"

as mentioned the number of rows vaires.

Or is there a way to loop through the cells and add the formula only to those where data exists?

Many thanks

 

[Loomah]

Hi
This will add the formula after the last column containing data. If you know how many columns you will have and that figure will be the same then it could be hard coded - post back if necessary.

This code will not overwrite any existing formulas but will add new ones at the end of the row.

Sub AddFormula()
Dim iCol As Integer
Dim lRow As Long

'find last row and column
With ActiveSheet.Cells
    lRow = .Find("*", Range("A1"), , , xlByRows, xlPrevious).Row
    iCol = .Find("*", Range("A1"), , , xlByColumns, xlPrevious).Column + 1
End With
'insert formula
With Cells(1, iCol)
    'use whatever formula you need here
    .FormulaR1C1 = "=SUM(RC[-" & iCol - 1 & "]:RC[-1])"
    .AutoFill Destination:=Range(Cells(1, iCol), Cells(lRow, iCol))
End With
End Sub

;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[catbert]

Thanks for that, works perfectly. Not sure I would have worked it out on my own, although now I know what I'm looking for I have seen your FAQ too.

The number of columns are currently fixed, but that may change. Could you tell me the amendments to hard code it for now.

How does the find command accomodate the fact that even if there are gaps in the first row - it stills puts the formula at the end of the data - I apologise if this is a daft question but I want to understand.

thanks again

 

[Loomah]

Hi
To hard code the rows you could either
a) Assign the number of columns to the variable iCol (ie iCol = 12) instead of letting the find function do it for you.
OR
b) Replace iCol altogether with the actual number, again eg 12. The tricky part in the above is the actual formula so...
"=SUM(RC[-" & iCol - 1 & "]:RC[-1])"
becomes
"=SUM(RC[-12]:RC[-1])"

If you are going to hard code it I'd go with option a as you only have to change one thing rather than searching for ecery instance where you need to refer to the column! But remember that if you have 12 cols of data to assign a value of 13 to iCol or the code will overwrite your data - not a good idea!

As for how the find works (not a daft question at all), have a look at the help (or the FAQ, I think!) for the find method as I've missed out the argument descriptions out of laziness today!

Basically what happens is you are looking for any character at all ("*" - wildcard) starting from cell A1. The key now is the search order and direction. For columns there's no need to seach rows hence "xlByColumns".

By using xlPrevious for the direction you'll have to imagine that find will go in a loop until something stops it. Here we're trying to find the previous column to A1 that contains data. As there is nothing prior to A1 find loops round and starts at the end again. In this instance it stops looking as soon as it finds something and this happens to be the last column containing data. Always!!!

Clear as mud? Sometimes I'm no good at explaining things and today I think is one of those times!

Post back if you would like further clarification.
Enjoy
;-)

If a man says something and there are no women there to hear him, is he still wrong?

 

[catbert]

actually that makes perfect sense, more so than excel help! so either my brain works the same way or I am having the usual mid week mental breakdown.

Thanks