Results 1 to 4 of 4

VBA - Using RANGE function - Feeding Parms Problem

Threaded View

  1. #1
    Registered User
    Join Date
    08-07-2007
    Posts
    14

    VBA - Using RANGE function - Feeding Parms Problem

    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
    Last edited by TreasureCat; 08-09-2007 at 02:29 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1