#### DKRbella0814

##### Board Regular

- Joined
- Aug 10, 2008

- Messages
- 155

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

End Sub