Run-time error 1004 on variable

[Casey]

Hi,
I've got an 1004 error showing up with the following line.

N = Wrks1.Cells(i, 1).Value

I've tried declaring the N variable as a string and as a variant, but no good. I know its probably something simple but I can't see it. Some help please.

Here is the entire code

Option Explicit

Sub ImportEstToProd()

Dim Wrks1 As Worksheet, Wrks2 As Worksheet, Wrks3 As Worksheet, Wrks5 As Worksheet
Dim CopyRow As Long
Dim Msg As Integer
Dim Response As Integer
Dim Entries As Long
Dim i As Long
Dim N As String


Msg = MsgBox("Estimates are complete and are ready" _
& " to be imported into the Production Tracking Sheet?" _
& (Chr(13)) & "Answering Yes will clear all previous daily tracking input!" _
& (Chr(13)) & "This action is not undoable!", _
vbYesNo + vbQuestion, "Import Estimate Information")
If Msg = 6 Then

'Application.ScreenUpdating = False
'Application.EnableEvents = False
'On Error GoTo RET

Set Wrks1 = Worksheets("Drywall set up sheet")
Set Wrks2 = Worksheets("Stucco set up sheet")
Set Wrks3 = Worksheets("Plaster set up sheet")
Set Wrks5 = Worksheets("Production")

Entries = Excel.WorksheetFunction.CountA(Wrks1.Range("LaborDBDW"))
Wrks5.Range("DailyProdInput").ClearContents
CopyRow = Wrks5.Range("ProductionTopRow").Row + 1
For i = 0 To Entries + 25
N = Wrks1.Cells(i, 1).Value
If N = "X" Then
With Wrks5
.Cells(CopyRow, 1).Value = Wrks1.Cells(i, 3).Value
.Cells(CopyRow, 2).Value = Wrks1.Cells(i, 2).Value
.Cells(CopyRow, 3).Value = Wrks1.Cells(i, 5).Value
.Cells(CopyRow + 1, 3).Value = Wrks1.Cells(i, 7).Value
End With
CopyRow = CopyRow + 3
End If
Next i
'RET:
'Application.EnableEvents = True
'Application.ScreenUpdating = True
End If
If Msg = 7 Then
Exit Sub

End If

End Sub

[Tom Ogilvy]

You are looping starting with zero
For i = 0 To Entries + 25
N = Wrks1.Cells(i, 1).Value

cells(0,1) is not legal.

loop from 1 to Entries + 25

--
Regards,
Tom Ogilvy


"Casey" wrote:

>
> Hi,
> I've got an 1004 error showing up with the following line.
>
> N = Wrks1.Cells(i, 1).Value
>
> I've tried declaring the N variable as a string and as a variant, but
> no good. I know its probably something simple but I can't see it. Some
> help please.
>
> Here is the entire code
>
> Option Explicit
>
> Sub ImportEstToProd()
>
> Dim Wrks1 As Worksheet, Wrks2 As Worksheet, Wrks3 As Worksheet, Wrks5
> As Worksheet
> Dim CopyRow As Long
> Dim Msg As Integer
> Dim Response As Integer
> Dim Entries As Long
> Dim i As Long
> Dim N As String
>
>
> Msg = MsgBox("Estimates are complete and are ready" _
> & " to be imported into the Production Tracking Sheet?" _
> & (Chr(13)) & "Answering Yes will clear all previous daily tracking
> input!" _
> & (Chr(13)) & "This action is not undoable!", _
> vbYesNo + vbQuestion, "Import Estimate Information")
> If Msg = 6 Then
>
> 'Application.ScreenUpdating = False
> 'Application.EnableEvents = False
> 'On Error GoTo RET
>
> Set Wrks1 = Worksheets("Drywall set up sheet")
> Set Wrks2 = Worksheets("Stucco set up sheet")
> Set Wrks3 = Worksheets("Plaster set up sheet")
> Set Wrks5 = Worksheets("Production")
>
> Entries = Excel.WorksheetFunction.CountA(Wrks1.Range("LaborDBDW"))
> Wrks5.Range("DailyProdInput").ClearContents
> CopyRow = Wrks5.Range("ProductionTopRow").Row + 1
> For i = 0 To Entries + 25
> N = Wrks1.Cells(i, 1).Value
> If N = "X" Then
> With Wrks5
> .Cells(CopyRow, 1).Value = Wrks1.Cells(i, 3).Value
> .Cells(CopyRow, 2).Value = Wrks1.Cells(i, 2).Value
> .Cells(CopyRow, 3).Value = Wrks1.Cells(i, 5).Value
> .Cells(CopyRow + 1, 3).Value = Wrks1.Cells(i, 7).Value
> End With
> CopyRow = CopyRow + 3
> End If
> Next i
> 'RET:
> 'Application.EnableEvents = True
> 'Application.ScreenUpdating = True
> End If
> If Msg = 7 Then
> Exit Sub
>
> End If
>
> End Sub
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=537003
>
>

[Jim Thomlinson]

Your counter i starts at zero. There is no row zero so the code crashes.
Change i to start at 1.
--
HTH...

Jim Thomlinson


"Casey" wrote:

>
> Hi,
> I've got an 1004 error showing up with the following line.
>
> N = Wrks1.Cells(i, 1).Value
>
> I've tried declaring the N variable as a string and as a variant, but
> no good. I know its probably something simple but I can't see it. Some
> help please.
>
> Here is the entire code
>
> Option Explicit
>
> Sub ImportEstToProd()
>
> Dim Wrks1 As Worksheet, Wrks2 As Worksheet, Wrks3 As Worksheet, Wrks5
> As Worksheet
> Dim CopyRow As Long
> Dim Msg As Integer
> Dim Response As Integer
> Dim Entries As Long
> Dim i As Long
> Dim N As String
>
>
> Msg = MsgBox("Estimates are complete and are ready" _
> & " to be imported into the Production Tracking Sheet?" _
> & (Chr(13)) & "Answering Yes will clear all previous daily tracking
> input!" _
> & (Chr(13)) & "This action is not undoable!", _
> vbYesNo + vbQuestion, "Import Estimate Information")
> If Msg = 6 Then
>
> 'Application.ScreenUpdating = False
> 'Application.EnableEvents = False
> 'On Error GoTo RET
>
> Set Wrks1 = Worksheets("Drywall set up sheet")
> Set Wrks2 = Worksheets("Stucco set up sheet")
> Set Wrks3 = Worksheets("Plaster set up sheet")
> Set Wrks5 = Worksheets("Production")
>
> Entries = Excel.WorksheetFunction.CountA(Wrks1.Range("LaborDBDW"))
> Wrks5.Range("DailyProdInput").ClearContents
> CopyRow = Wrks5.Range("ProductionTopRow").Row + 1
> For i = 0 To Entries + 25
> N = Wrks1.Cells(i, 1).Value
> If N = "X" Then
> With Wrks5
> .Cells(CopyRow, 1).Value = Wrks1.Cells(i, 3).Value
> .Cells(CopyRow, 2).Value = Wrks1.Cells(i, 2).Value
> .Cells(CopyRow, 3).Value = Wrks1.Cells(i, 5).Value
> .Cells(CopyRow + 1, 3).Value = Wrks1.Cells(i, 7).Value
> End With
> CopyRow = CopyRow + 3
> End If
> Next i
> 'RET:
> 'Application.EnableEvents = True
> 'Application.ScreenUpdating = True
> End If
> If Msg = 7 Then
> Exit Sub
>
> End If
>
> End Sub
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=537003
>
>

[Casey]

Tom & Jim,
Exactly the solution. I'm just staring to get the hang of looping with variables and I make elementry mistakes constantly. Your prescription cured my OP but revealed another problem with the routine. If I could ask a follow up question?

The line I use to define Entries;
Entries = Excel.WorksheetFunction.CountA(Wrks1.Range("LaborDBDW"))
contains a name range ("LaborDBDW") that gets moved up and down because of Rows being inserted and deleted above it on the worksheet (might be Rows 15-35 one time and Rows 14-34 the next time). However, when the macro runs it picks up at Row 6 which is definately outside ("LaborDBDW"). Could I name the row at the top of ("LaborDBDW") and use that in the For i = 1 To Entries + 25 somehow so it stars reading info in the correct place?

[Casey]

Tom & Jim,
Thank you very much for your reply to my original post. I work on the solution to the second problem using the idea I had and it worked. Thanks again for the help.

[Tom Ogilvy]

Dim rng as Range
set rng = Wrks1.Range("LaborDBDW")
for i = rng.row to Entries + 25


if you put row on a multicell range, it returns the row of the first cell in
the range

if you want the last row

msgbox rng.rows(rng.rows.count).Row

I just set rng to Wrks1.Range("LaborDBDW") to save typing. You can use it
directly.

If you really want to loop through that range

for each cell in Wrks1.Range("LaborDBDW")
msgbox cell.Value & " - " & cell.Address(0,0) & " - " & cell.row
Next

as an example.

--
regards,
Tom Ogilvy



"Casey" <[email protected]> wrote in
message news:[email protected]...
>
> Tom & Jim,
> Exactly the solution. I'm just staring to get the hang of looping with
> variables and I make elementry mistakes constantly. Your prescription
> cured my OP but revealed another problem with the routine. If I could
> ask a follow up question?
>
> The line I use to define Entries;
> Entries = Excel.WorksheetFunction.CountA(Wrks1.Range("LaborDBDW"))
> contains a name range ("LaborDBDW") that gets moved up and down because
> of Rows being inserted and deleted above it on the worksheet (might be
> Rows 15-35 one time and Rows 14-34 the next time). However, when the
> macro runs it picks up at Row 6 which is definately outside
> ("LaborDBDW"). Could I name the row at the top of ("LaborDBDW") and
> use that in the For i = 1 To Entries + 25 somehow so it stars reading
> info in the correct place?
>
>
> --
> Casey
>
>
> ------------------------------------------------------------------------
> Casey's Profile:
http://www.excelforum.com/member.php...fo&userid=4545
> View this thread: http://www.excelforum.com/showthread...hreadid=537003
>

[Casey]

Tom,
Thank you for lesson. I have been slamming bits of code together I find on the forums for two or three years now and it has saved me so much time. It's given me a real desire to learn the best, most economical practices for my code.
I am really grateful when someone takes the time to explain something so clearly and completely. I got my code to work, but it's so convoluted compared to yours I'm going to re-write it to incorporate your much more elegant method. Thank you again.