Problem with the end of this code

Hello,

This code transfers data from worksheetcopy to tithesrecord, then again from worksheetcopy (a different range) to offering record.

The error at the end of the code said compile error: For without Next (End Sub is highlighted)
I can't find the problem.

Sub TransferNames_Tithes()
Application.ScreenUpdating = False
Range("A5:A29").Copy
Range("A54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F5:F29").Select
Application.CutCopyMode = False
Selection.Copy
Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C5:C29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H5:H29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A54:A103").Select
Selection.Font.Bold = True
Range("B54:B103").Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.Bold = True
Range("C52").Select

'subroutine to transfer names & amounts to Tithes Record Sheet
With Sheets("TithesRecord")
Range("a54:a154").Copy .Range("a2")
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
.Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
For Each c In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
x = .Columns(1).Find(c).Row

Cells(c.Row, 2).Copy
.Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Range("a54:b154").Select
Selection.Delete
Range("e2").Select

'subroutine to transfer names & offering to table below worksheet
Range("A5:A29").Copy
Range("A54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F5:F29").Select
Application.CutCopyMode = False
Selection.Copy
Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D5:D29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I5:I29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A54:A103").Select
Selection.Font.Bold = True
Range("B54:B103").Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.Bold = True
Range("C52").Select

'subroutine to transfer names & amounts to Tithes Record Sheet
With Sheets("OfferingRecord")
Range("a54:a154").Copy .Range("a2")
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
.Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
For Each i In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
x = .Columns(1).Find(i).Row

Cells(i.Row, 2).Copy
.Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Range("a54:b154").Select
Selection.Delete
Range("e2").Select
Next
End With

End Sub

Thanks

OK here's an update to this code. I think I solved the end if problem.

Now a new error: runtime error 13, type mismatch (below is whats highlighted)

.Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7

Code:

Sub TransferNames_Tithes()
Application.ScreenUpdating = False
Range("A5:A29").Copy
Range("A54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F5:F29").Select
Application.CutCopyMode = False
Selection.Copy
Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("C5:C29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H5:H29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A54:A103").Select
Selection.Font.Bold = True
Range("B54:B103").Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.Bold = True
Range("C52").Select

'subroutine to transfer names & amounts to Tithes Record Sheet
With Sheets("TithesRecord")
Range("a54:a154").Copy .Range("a2")
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
.Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
For Each c In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
x = .Columns(1).Find(c).Row

Cells(c.Row, 2).Copy
.Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Range("a54:b154").Select
Selection.Delete
Range("e2").Select
Next
End With
'subroutine to transfer names & offering to table below worksheet
Range("A5:A29").Copy
Range("A54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("F5:F29").Select
Application.CutCopyMode = False
Selection.Copy
Range("A79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D5:D29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("I5:I29").Select
Application.CutCopyMode = False
Selection.Copy
Range("B79").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A54:A103").Select
Selection.Font.Bold = True
Range("B54:B103").Select
Selection.NumberFormat = "$#,##0.00"
Selection.Font.Bold = True
Range("C52").Select

'subroutine to transfer names & amounts to Tithes Record Sheet
With Sheets("OfferingRecord")
Range("a54:a154").Copy .Range("a2")
lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
.Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
For Each i In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
x = .Columns(1).Find(i).Row

Cells(i.Row, 2).Copy
.Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
Next
End With

End Sub

Thanks,
EMoe

..Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
The problem here is that on the left of the = the result is a Range; on the
right of the = the result would be interpreted as a number (i.e., when you do
..Cells(1, lastcol) + 7 VBA will interpret this to mean "the value in
cell(1,lastcol) plus 7." It is not clear to me if you really mean to do a
numerical calculation:
..Cells(1, lastcol + 1).Value = .Cells(1, lastcol).Value + 7
or if you really meant to do this:
Cells(1, lastcol + 1) = .Cells(1, lastcol + 7)



"EMoe" wrote:

>
> OK here's an update to this code. I think I solved the end if problem.
>
> Now a new error: runtime error 13, type mismatch (below is whats
> highlighted)
>
> .Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
>
> Code:
>
> Sub TransferNames_Tithes()
> Application.ScreenUpdating = False
> Range("A5:A29").Copy
> Range("A54").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("F5:F29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("A79").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("C5:C29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B54").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("H5:H29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B79").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A54:A103").Select
> Selection.Font.Bold = True
> Range("B54:B103").Select
> Selection.NumberFormat = "$#,##0.00"
> Selection.Font.Bold = True
> Range("C52").Select
>
> 'subroutine to transfer names & amounts to Tithes Record Sheet
> With Sheets("TithesRecord")
> Range("a54:a154").Copy .Range("a2")
> lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
> .Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
> For Each c In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
> x = .Columns(1).Find(c).Row
>
> Cells(c.Row, 2).Copy
> .Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
> Application.CutCopyMode = False
> Range("a54:b154").Select
> Selection.Delete
> Range("e2").Select
> Next
> End With
> 'subroutine to transfer names & offering to table below worksheet
> Range("A5:A29").Copy
> Range("A54").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("F5:F29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("A79").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("D5:D29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B54").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("I5:I29").Select
> Application.CutCopyMode = False
> Selection.Copy
> Range("B79").Select
> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A54:A103").Select
> Selection.Font.Bold = True
> Range("B54:B103").Select
> Selection.NumberFormat = "$#,##0.00"
> Selection.Font.Bold = True
> Range("C52").Select
>
> 'subroutine to transfer names & amounts to Tithes Record Sheet
> With Sheets("OfferingRecord")
> Range("a54:a154").Copy .Range("a2")
> lastcol = .Cells.SpecialCells(xlCellTypeLastCell).Column
> .Cells(1, lastcol + 1) = .Cells(1, lastcol) + 7
> For Each i In Range("a54:a" & Cells(Rows.Count, 1).End(xlUp).Row)
> x = .Columns(1).Find(i).Row
>
> Cells(i.Row, 2).Copy
> .Cells(x, lastcol + 1).PasteSpecial Paste:=xlPasteAll
> Application.CutCopyMode = False
> Next
> End With
>
> End Sub
>
> Thanks,
> EMoe
>
>
> --
> EMoe
> ------------------------------------------------------------------------
> EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183
> View this thread: http://www.excelforum.com/showthread...hreadid=378443
>
>