Need help writing Macro!

[DKRbella0814]

I have created a simple calculator within a workbook

One sheet is set up for the calculator and is named calculator (revenues , expenses, net income)

Another sheet is named list

The sheet named List contains all descriptive categories from the calculator spreadsheet (i.e. DVD # monthly rentals, Cost for 1st day, additional day fee, etc.)

The reason why I created the list sheet is to record all of the "base case" variables that were used to calculate our projected break even for a particular investment. The calculator allows you to manipulate each of the variables in the calculator (including some of the descriptive categories named above).

I wanted to be able to restore the calculator with the initial variables that we ran our business case scenario from, even if variables are changed by the end-user to model different scenarios. Since this is a repetitive process of re-entering the base #s used in the calculator, I thought a macro would be appropriate.

Please review the macro below and see if you can help me determine why it is not running correctly. All I want to do is simply pull data from the List spreadsheet and locate the data in specific cells in the simple calculator on the calculator sheet. When I try to run the macro below, I keep getting an error message.

The problem seems to be in the bold lines below....

Sub BaseScenario()
'
' Keyboard Shortcut: Ctrl+r
' Dim X As Integer
Dim N As Integer

Dim List As Worksheet
Dim Calculator As Worksheet

Set List = Worksheets("List")
Set Calculator = Worksheets("Calculator")

List.Activate

N = ActiveCell.Row

For X = 1 To 111
Select Case X
Case 1
Calculator.Cells(19, 2) = List.Cells(N, 3) 'First Night Rental Fee
Case 2
Calculator.Cells(20, 2) = List.Cells(N, 4) 'Additional Day Fee
Case 3
Calculator.Cells(21, 2) = List.Cells(N, 5) 'Rentals/Day
Case 4
Calculator.Cells(22, 2) = List.Cells(N, 6) 'Average Rental Period (days)
Case 5
Calculator.Cells(26, 2) = List.Cells(N, 9) 'First Night Rental Fee
Case 6
Calculator.Cells(27, 2) = List.Cells(N, 10) 'Additional Day Fee
Case 7
Calculator.Cells(28, 2) = List.Cells(N, 11) 'Rentals/Day
Case 8
Calculator.Cells(29, 2) = List.Cells(N, 12) 'Average Rental Period (days)
Case 9
Calculator.Cells(33, 2) = List.Cells(N, 15) 'First Night Rental Fee
Case 10
Calculator.Cells(34, 2) = List.Cells(N, 16) 'Additional Day Fee
Case 11
Calculator.Cells(35, 2) = List.Cells(N, 17) 'Rentals/Day
Case 12
Calculator.Cells(36, 2) = List.Cells(N, 18) 'Average Rental Period (days)
Case 13
Calculator.Cells(40, 2) = List.Cells(N, 21) 'First Night Rental Fee
Case 14
Calculator.Cells(41, 2) = List.Cells(N, 22) 'Additional Day Fee
Case 15
Calculator.Cells(42, 2) = List.Cells(N, 23) 'Rentals/Day
Case 16
Calculator.Cells(43, 2) = List.Cells(N, 24) 'Average Rental Period (days)
Case 17
Calculator.Cells(47, 2) = List.Cells(N, 27) 'First Night Rental Fee
Case 18
Calculator.Cells(48, 2) = List.Cells(N, 28) 'Additional Day Fee
Case 19
Calculator.Cells(49, 2) = List.Cells(N, 29) 'Rentals/Day
Case 20
Calculator.Cells(50, 2) = List.Cells(N, 30) 'Average Rental Period (days)
Case 21
Calculator.Cells(58, 2) = List.Cells(N, 34) '# Sold per Mo.
Case 22
Calculator.Cells(59, 2) = List.Cells(N, 35) 'Resale Value
Case 23
Calculator.Cells(61, 2) = List.Cells(N, 37) '# Sold per Mo.
Case 24
Calculator.Cells(62, 2) = List.Cells(N, 38) 'Resale Value
Case 25
Calculator.Cells(64, 2) = List.Cells(N, 40) '# Sold per Mo.
Case 26
Calculator.Cells(65, 2) = List.Cells(N, 41) 'Resale Value
Case 27
Calculator.Cells(67, 2) = List.Cells(N, 43) '# Sold per Mo.
Case 28
Calculator.Cells(68, 2) = List.Cells(N, 44) 'Resale Value
Case 29
Calculator.Cells(70, 2) = List.Cells(N, 46) '# Sold per Mo.
Case 30
Calculator.Cells(71, 2) = List.Cells(N, 47) 'Resale Value
Case 31
Calculator.Cells(79, 2) = List.Cells(N, 51) 'Monthly Rate -10 sec Spot
Case 32
Calculator.Cells(80, 2) = List.Cells(N, 52) '# Sold per Mo.
Case 33
Calculator.Cells(81, 2) = List.Cells(N, 53) 'Monthly Rate - 30 sec Spot
Case 34
Calculator.Cells(82, 2) = List.Cells(N, 54) '# Sold per Mo.
Case 35
Calculator.Cells(83, 2) = List.Cells(N, 55) 'Monthly Rate -60 sec Spot
Case 36
Calculator.Cells(84, 2) = List.Cells(N, 56) '# Sold per Mo.
Case 37
Calculator.Cells(89, 2) = List.Cells(N, 60) 'Monthly Rate
Case 38
Calculator.Cells(90, 2) = List.Cells(N, 61) '# Sold per Mo.
Case 39
Calculator.Cells(91, 2) = List.Cells(N, 62) '3 Mo. Contract
Case 40
Calculator.Cells(92, 2) = List.Cells(N, 63) '# Sold per Mo.
Case 41
Calculator.Cells(97, 2) = List.Cells(N, 67) 'Price per Lot (250 lot size)
Case 42
Calculator.Cells(98, 2) = List.Cells(N, 68) '# Lots Sold per Mo.
Case 43
Calculator.Cells(20, 6) = List.Cells(N, 72) '% of Transactions
Case 44
Calculator.Cells(21, 6) = List.Cells(N, 73) 'Avg Cost to Repair
Case 45
Calculator.Cells(27, 6) = List.Cells(N, 75) '% Commission
Case 46
Calculator.Cells(32, 6) = List.Cells(N, 77) 'Authorization Fee (VISA, MC, Discover)
Case 47
Calculator.Cells(33, 6) = List.Cells(N, 78) 'Gateway Fee (Per Authorization)
Case 48
Calculator.Cells(34, 6) = List.Cells(N, 79) 'Interchange Fee
Case 49
Calculator.Cells(36, 6) = List.Cells(N, 81) 'Chargebacks
Case 50
Calculator.Cells(37, 6) = List.Cells(N, 82) 'NSF Charge
Case 51
Calculator.Cells(38, 6) = List.Cells(N, 83) 'Minimum Monthly Discount
Case 52
Calculator.Cells(22, 10) = List.Cells(N, 88) 'Staple
Case 53
Calculator.Cells(23, 10) = List.Cells(N, 89) 'Blue Ray Disc- New
Case 54
Calculator.Cells(24, 10) = List.Cells(N, 90) 'Blue Ray Disc- Used
Case 55
Calculator.Cells(25, 10) = List.Cells(N, 91) 'Video Game- New
Case 56
Calculator.Cells(26, 10) = List.Cells(N, 92) 'Video Game- Used
Case 57
Calculator.Cells(21, 12) = List.Cells(N, 94) 'New Release
Case 58
Calculator.Cells(22, 12) = List.Cells(N, 95) 'Staple
Case 59
Calculator.Cells(23, 12) = List.Cells(N, 96) 'Blue Ray Disc- New
Case 60
Calculator.Cells(24, 12) = List.Cells(N, 97) 'Blue Ray Disc- Used
Case 61
Calculator.Cells(25, 12) = List.Cells(N, 98) 'Video Game- New
Case 62
Calculator.Cells(26, 12) = List.Cells(N, 99) 'Video Game- Used
Case 63
Calculator.Cells(42, 13) = List.Cells(N, 101) 'Monthly Service Fee
Case 64
Calculator.Cells(45, 10) = List.Cells(N, 102) 'Application Fee (One-Time)
Case 65
Calculator.Cells(46, 12) = List.Cells(N, 103) 'Initial S/U Fee (One-Time)
Case 66
Calculator.Cells(50, 12) = List.Cells(N, 105) 'Annual Maintenance Fee
Case 67
Calculator.Cells(54, 10) = List.Cells(N, 107) '# Field Reps
Case 68
Calculator.Cells(54, 11) = List.Cells(N, 108) '# Hrs/Week
Case 69
Calculator.Cells(54, 12) = List.Cells(N, 109) 'Rate
Case 70
Calculator.Cells(57, 13) = List.Cells(N, 111) 'Annual Cost per Kiosk

End Select
Next X


End Sub

 

[ravishankar]

Hi
you can try these codes

Sub DKRB()
Dim a As Long, b As Long, n As Long
Dim c As String
n = ActiveCell.Row
        For a = 1 To 10
        b = Choose(a, 3, 4, 5, 6, 9, 10, 11, 12, 15, 16)
        c = Choose(a, "B19", "B20", "B21", "B22", "B26", "B27", "B28", "B29", "B33", "B34")
        Sheets("Calculator").Range(c) = Sheets("List").Cells(n, b)
        Next a
MsgBox "Complete"
End Sub

It works for 10 cases which can be extended.
ravi