Macro to copy text from one sheet to another fails

[Roccofan]

The following macro fails with the statement next to the last statement that reads:

Range(p).Value = v

The error message reads:

Runtime error '1004'

Method 'Range' of object '_Global' failed

I copied this code from another workbook, and it worked fine there. The only changes that were made were changing the sheet names and changing the ranges from 13 to 17 rows and columns.

Any help would be appreciated.

RF

Sub UpdateData()

Dim p(17)
Dim v(17)

Application.ScreenUpdating = False

' Find last line on database

Sheets("Mutual Fund Data").Activate
Range("B8").Select
Selection.End(xlDown).Select

' Establish what row the next row is

nr = ActiveCell.Row + 1

' Establish the ranges that will have the new values pasted into
' e.g. p(1) will hold the value B8 ie the cell address for the 1st
' piece of information to be stored to. (nr being the "New Row" number)

p(1) = "B" & nr
p(2) = "C" & nr
p(3) = "D" & nr
p(4) = "E" & nr
p(5) = "F" & nr
p(6) = "G" & nr
p(7) = "H" & nr
p(8) = "I" & nr
p(9) = "J" & nr
p(10) = "K" & nr
p(11) = "L" & nr
p(12) = "M" & nr
p(13) = "N" & nr
p(14) = "O" & nr
p(15) = "P" & nr
p(16) = "Q" & nr
p(17) = "R" & nr

' Load the values from the input forms to variables v1-13

Sheets("Input").Activate
v(1) = Range("D3").Value
v(2) = Range("D5").Value
v(3) = Range("D7").Value
v(4) = Range("D9").Value
v(5) = Range("D11").Value
v(6) = Range("D13").Value
v(7) = Range("D15").Value
v(8) = Range("D17").Value
v(9) = Range("D19").Value
v(10) = Range("D21").Value
v(11) = Range("D23").Value
v(12) = Range("D25").Value
v(13) = Range("D27").Value
v(14) = Range("D29").Value
v(15) = Range("D31").Value
v(16) = Range("D33").Value
v(17) = Range("D35").Value

' Store the values of these variables to the places calculated above

Sheets("Mutual Fund Data").Activate
For n = 1 To 17
Range(p).Value = v
Next

Application.ScreenUpdating = True

End Sub
This message was edited by Roccofan on 2002-09-19 08:56

 

[Andrew Poulsom]

is there any data below cell B8? If not End Down will go to row 65536 and nr will be 65537. Range B65537 does not exist.

Better to use:

Range("B65536").End(xlUp).Select

 

[Roccofan]

The really really really pathetic part about this is that everytime I copy this macro I have THE EXACT SAME F@#$KING issue. Everytime I solve it, I go right back and get confused all over again.

Thank you very much for your help.

Mr. Excel: Who should I turn my "Company Excel Guru" title over to? I'm obviously unqualified to hold the position.

RF