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("LaborD BDW"))
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

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("LaborD BDW"))
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

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("LaborD BDW"))
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

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("LaborD BDW"))
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

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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=537003

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" wrote in
message ...

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("LaborD BDW"))
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

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.


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=537003