+ Reply to Thread
Results 1 to 2 of 2

Need help using VBA to do excel function with variable parameters

  1. #1
    Registered User
    Join Date
    10-08-2016
    Location
    usa
    MS-Off Ver
    2012
    Posts
    2

    Need help using VBA to do excel function with variable parameters

    Hi everyone!

    I'm new to VBA and this site. Everyone seems really nice on here and I hope someone will be able to help me with my situation.

    I am trying to write a macro that will automatically perform a FORECAST function using visible cells from 3 columns. I'm not sure how to store a variable range and input into the forecast function.
    I know I need 3 variables:
    "=FORECAST=(x, knownY, knownX)"

    I figured out how to select the visible cells in each of the columns I need , but they don't seem to get stored. Below is my current non-working macro. THANKS!!


    Dim Limit As Long
    Dim knownY As Long
    Dim knownX As Long

    With ActiveSheet

    Limit = .Cells(.Rows.Count, 4).End(xlUp).Row

    If Limit < 3 Then Exit Sub

    .Cells(1, 20).Offset(1, 0).Resize(Limit - 1).SpecialCells(xlCellTypeVisible).Select ' = select coulmn T visible cells only
    End With

    With ActiveSheet

    knownY = .Cells(.Rows.Count, 4).End(xlUp).Row

    If knownY < 3 Then Exit Sub

    .Cells(1, 6).Offset(1, 0).Resize(knownY - 1).SpecialCells(xlCellTypeVisible).Select ' = select coulmn F visible cells only
    End With


    With ActiveSheet

    knownX = .Cells(.Rows.Count, 4).End(xlUp).Row

    If knownX < 3 Then Exit Sub

    .Cells(1, 23).Offset(1, 0).Resize(knownX - 1).SpecialCells(xlCellTypeVisible).Select ' = select coulmn W visible cells only
    End With
    '
    Sheets("Sheet2").Select
    ActiveCell.FormulaR1C1 = "=FORECAST(Limit,knownY,knownX)"
    Range("B9").Select

  2. #2
    Registered User
    Join Date
    10-08-2016
    Location
    usa
    MS-Off Ver
    2012
    Posts
    2

    Re: Need help using VBA to do excel function with variable parameters

    So I think I need to and some range variables in here. I'm thinking that I might use my current code and just add a function to make the current selected range equal to each variable.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Using a variable as an optional parameters default
    By kadeo in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-21-2016, 12:12 PM
  2. Worksheet function with variable number of parameters
    By amphinomos in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 03-15-2016, 10:33 AM
  3. Running a variable module with parameters
    By UML in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-04-2014, 05:22 AM
  4. Variable passing of parameters in a macro
    By gswhoop in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 12-05-2007, 03:25 AM
  5. [SOLVED] OnAction of Menu Bar with variable parameters
    By chris in forum Excel General
    Replies: 1
    Last Post: 08-07-2006, 11:10 AM
  6. Replies: 0
    Last Post: 07-17-2006, 12:10 PM
  7. Replies: 2
    Last Post: 02-28-2006, 01:15 PM

Tags for this Thread

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