VB macros & "relative positioning"
Dim LastRow as long
with activesheet lastrow = .cells(.rows.count,"A").end(xlup).row .columns(1).insert .range("A1:A" & lastrow).FormulaR1C1 = "=IF(RC[1]<25,1,0)" end with And personally, I like to use excel's built in Data|subtotals whenever possible. If I have to add a formula to put an identifier on that row, that's ok with me, too. Jennifer wrote: I want to write a VB macro that will insert a column at the beginning of the spreadsheet, add a formula that deals with another cell on the same row, and then copy the formula for every row that has data in it. (Each row with data will have data in the original column A). Because there may be a different number of rows each time, I want to have the macro recognise relative positioning (I think that's the correct term). Namely, if I can get to the last row that has data, I want to move one cell to the right. However, when I record the macro, it identifies a specific cell (in this case, cell A31. Any suggestions? Existing code: Columns("A:A").Select Selection.Insert Shift:=xlToRight Range("A1").Select ActiveCell.FormulaR1C1 = "=IF(RC[1]<25,1,0)" Range("A1").Select Selection.Copy Range("B1").Select Selection.End(xlDown).Select Range("A31").Activate Range(Selection, Selection.End(xlUp)).Select ActiveSheet.Paste Range("A1").Select In addition, what I would ultimately like to do is to have the macro look at the contents of each non-empty cell in (the original) column A to see if it matches a specific work unit. (I have a table with all of the work units, and have a formula which works using IF and MATCH). For each row identified as being a work unit, I want to bold that row and add two rows above it, and then add formulae (for totals) in the row above. Any hints how this could be done? -- Dave Peterson |
All times are GMT +1. The time now is 03:09 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com