Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 -- EMoe ------------------------------------------------------------------------ EMoe's Profile: http://www.excelforum.com/member.php...o&userid=23183 View this thread: http://www.excelforum.com/showthread...hreadid=378443 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with the end of this code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problem with the end of this code
..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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code problem | Excel Discussion (Misc queries) | |||
Code Problem | Excel Programming | |||
code problem | Excel Programming | |||
VBA code problem | Excel Programming | |||
Problem with this code | Excel Programming |