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
Bookmarks