Problem with the end of this code

[EMoe]

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("D529").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

 

[EMoe]

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("D529").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

 

[Guest]

..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)