Run-time error 1004

[JimFor]

Hi,

I'm working on a program and have received the following error message when I
try to run it.

"Run-time error 104
Application-defined or object-defined error"

I am trying to use a counter to transfer cell data from sheet 1 to sheet 2.
For example, I have data in the first five rows in column A. I want to, say,
transter the data from the last four rows in Col A to sheet 2. But I will do
it according which cell is made active based on a "counter" value. Right now
I am only writing part of that part of the program. Here is my code:

Sub MySub()
'Transfer sheet data according to i
Dim Total As Double
Dim i As Integer
i = 1
Total = 1
For i = 1 To 3
Total = Total + i
Fred
Next i
End Sub

Sub Fred()

Worksheets("Sheet2").Cells(Total, 1).Value = Worksheets("Sheet1").Cells(Total,
1).Value

End Sub



For example, when the counter is 2 the value in cell (2,1) in sheet 1 is placed
in cell (2,1) on sheet 2. When the counter is 4, the value in cell (4,1) in
sheet 1 is placed in cell (4,1). Hope this is clear. Can anyone tell me what
I am doing wrong and what I can do to fix it?

Thanks

 

[JE McGimpsey]

Variables are local when declared inside a procedure, so Fred doesn't
understand what Total is (if you had Option Explicit at the top of your
module, you would have gotten a warning that Total was an undeclared
variable). You can either declare Total at the top of the module,
outside a procedure (making it global) or declare Fred() to take an
argument:

Public Sub Fred(ByVal dTotal As Double)
Worksheets("Sheet2").Cells(dTotal, 1).Value = _
Worksheets("Sheet1").Cells(dTotal, 1).Value
End Sub


then call Fred like this:

Total = Total + i
Fred Total

 

[Alain CROS]

Hi
Use Option Explicit

In a module

Option Explicit
Dim Total As Double

Sub MySub()
'Transfer sheet data according to i
Dim i As Integer
Total = 1
For i = 1 To 3
Total = Total + i
Fred
Next i
End Sub

Sub Fred()
Worksheets(2).Cells(Total, 1).Value = Worksheets(1).Cells
(Total, 1).Value
End Sub

Alain CROS

-----Original Message-----
Hi,

I'm working on a program and have received the following error message when I
try to run it.

"Run-time error 104
Application-defined or object-defined error"

I am trying to use a counter to transfer cell data from sheet 1 to sheet 2.
For example, I have data in the first five rows in column A. I want to, say,
transter the data from the last four rows in Col A to sheet 2. But I will do
it according which cell is made active based on

a "counter" value. Right now

 

[JimFor]

Thanks to you both.

 

[JimFor]

Still had the same error message after putting in an Option Explicit line Did
some more research. If anyone is interested, here is what seemed to be what
was wrong. In addition to not including "Option Explicit," I was passing an
argument to Fred but did not tell Fred what it was. There was nothing in the
parenthesis. This is the new program which seems to work. Note that I tell
Fred to expect the value in "Total" by putting "Total" inside the parentheses
after the word "Fred." Thanks for helping me think in the right direction.


Option Explicit
Sub MySub()
'Transfer sheet data according to i
Dim Total As Double
Dim i As Integer
Total = 0
For i = 1 To 3
Total = Total + i
Fred (Total)
Next i
End Sub

Sub Fred(Total)
Worksheets("Sheet2").Cells(Total, 1).Value = Worksheets("Sheet1").Cells(Total,
1).Value
End Sub