I have searched here and I found some threads which I thought might help but after trying I came up empty so I figured I'd start one and put my code up.
I've gone from Green Screen Programming back to the GUI side and now have to learn the VBA for EXCEL....
Task: On a worksheet which has a dynamically changing # of rows, insert 2 columns, set their width, and insert a formula into the range.
- For example if there are 30 rows the variable RowsCounted will house 30. If there are 20,000 then RowsCounted will have the value of 20,000. I want to say Range("F3:RowsCounted".Formula = "FormulaGoesHere"
Here is the specific line which is giving me problems:
Range("F3: & RowsCounted &").Formula = "=ROUND((RC[4]/RC[-1]),0)" ' 4 columns to the right of F is Column J
Here is what I have thus far (it all works except being able to put something into the range):
Sub NewRows()
Dim StringLength As Integer
' Inserts two new columns [F & H]
Columns("F:F").Select
Selection.Insert Shift:=xlToRight
Columns("H:H").Select
Selection.Insert Shift:=xlToRight
Range("F3").ColumnWidth = 8 'Sets Colum Width to 8
Range("H3").ColumnWidth = 8 'Sets Colum Width to 8
' Finds the number of rows in the spreadsheet
RowsCounted = ActiveSheet.UsedRange.Rows.Count 'Returns the # of rows
ActiveCell.SpecialCells(xlLastCell).Select
' Assigns the last row to columns F & H
LastFRow = "F" & RowsForFormula 'concatenates the F & # of rows
LastHRow = "H" & RowsForFormula 'concatenates the H & # of rows
'This puts the formula J#/E# into column F for rows 3-RowsCounted
Range("F3: & RowsCounted &").Formula = "=ROUND((RC[4]/RC[-1]),0)" ' 4 columns to the right of F is Column J
Range("H3:H5").Formula = "=ROUND (RC[3]/RC[-1]),0)" ' 3 columns to the right of H is Column K
End Sub
Edited to reflect the code tags
Bookmarks