copy and paste cells non-randomly form range
I have a range of cells like A1:D200 I want a macro that copy and paste A1 in
E1 only then you run the macro again and it shows A2 in E1 so on till it reaches D200 and when i close excel and reopen it it should start from where it left off. Please Help thx. -- Message posted via http://www.officekb.com |
copy and paste cells non-randomly form range
Just to let you know you can stop a macro from either the worksheet or VBA by
typing CNTR-BREAK. You can have the code continue by going to VBA and typing F5. Here is the code you are looking for: Sub ContinueProcessing() Const Lastrow = 200 Const LastColumn = "D" Const ColANumber = 1 Dim ColLetter As String If IsEmpty(Range("E1").Value) Then Cells("E1") = "A1" LastCell = Range("E1").Value ColLetter = "" Do While Not IsNumeric(Left(LastCell, 1)) ColLetter = ColLetter & Left(LastCell, 1) LastCell = Mid(LastCell, 2) Loop RowNumber = Val(LastCell) StartCol = ColLetter For RowCount = RowNumber To Lastrow Set ColumnRange = Range(Cells(RowCount, StartCol), _ Cells(RowCount, LastColumn)) For Each cell In ColumnRange 'enter your code here ColLetter = ConvertColtoLetter(cell.Column) Range("E1").Value = ColLetter & RowCount Next cell StartCol = "A" Next RowCount End Sub Function ConvertColtoLetter _ (ColNumber As Integer) As String FirstBit = Int(ColNumber / 26) SecondBit = ColNumber Mod 26 If FirstBit = 0 Then ConvertColtoLetter = Chr(Asc("A") + SecondBit - 1) Else ConvertColtoLetter = _ Chr(Asc("A") + FirstBit - 1) Chr (Asc("A") + SecondBit - 1) End If End Function "saman110 via OfficeKB.com" wrote: I have a range of cells like A1:D200 I want a macro that copy and paste A1 in E1 only then you run the macro again and it shows A2 in E1 so on till it reaches D200 and when i close excel and reopen it it should start from where it left off. Please Help thx. -- Message posted via http://www.officekb.com |
copy and paste cells non-randomly form range
Hello Joel,
I get an error when I run the macro it says "Type mismatch". Joel wrote: Just to let you know you can stop a macro from either the worksheet or VBA by typing CNTR-BREAK. You can have the code continue by going to VBA and typing F5. Here is the code you are looking for: Sub ContinueProcessing() Const Lastrow = 200 Const LastColumn = "D" Const ColANumber = 1 Dim ColLetter As String If IsEmpty(Range("E1").Value) Then Cells("E1") = "A1" LastCell = Range("E1").Value ColLetter = "" Do While Not IsNumeric(Left(LastCell, 1)) ColLetter = ColLetter & Left(LastCell, 1) LastCell = Mid(LastCell, 2) Loop RowNumber = Val(LastCell) StartCol = ColLetter For RowCount = RowNumber To Lastrow Set ColumnRange = Range(Cells(RowCount, StartCol), _ Cells(RowCount, LastColumn)) For Each cell In ColumnRange 'enter your code here ColLetter = ConvertColtoLetter(cell.Column) Range("E1").Value = ColLetter & RowCount Next cell StartCol = "A" Next RowCount End Sub Function ConvertColtoLetter _ (ColNumber As Integer) As String FirstBit = Int(ColNumber / 26) SecondBit = ColNumber Mod 26 If FirstBit = 0 Then ConvertColtoLetter = Chr(Asc("A") + SecondBit - 1) Else ConvertColtoLetter = _ Chr(Asc("A") + FirstBit - 1) Chr (Asc("A") + SecondBit - 1) End If End Function I have a range of cells like A1:D200 I want a macro that copy and paste A1 in E1 only then you run the macro again and it shows A2 in E1 so on till it reaches D200 and when i close excel and reopen it it should start from where it left off. Please Help thx. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200708/1 |
copy and paste cells non-randomly form range
I need two things
1) Which line of VBA code is highlighted 2) If you highlight each variable (on the failed line) with the mouse, right click mouse and add to ewatch. Let me know the values. "saman110 via OfficeKB.com" wrote: Hello Joel, I get an error when I run the macro it says "Type mismatch". Joel wrote: Just to let you know you can stop a macro from either the worksheet or VBA by typing CNTR-BREAK. You can have the code continue by going to VBA and typing F5. Here is the code you are looking for: Sub ContinueProcessing() Const Lastrow = 200 Const LastColumn = "D" Const ColANumber = 1 Dim ColLetter As String If IsEmpty(Range("E1").Value) Then Cells("E1") = "A1" LastCell = Range("E1").Value ColLetter = "" Do While Not IsNumeric(Left(LastCell, 1)) ColLetter = ColLetter & Left(LastCell, 1) LastCell = Mid(LastCell, 2) Loop RowNumber = Val(LastCell) StartCol = ColLetter For RowCount = RowNumber To Lastrow Set ColumnRange = Range(Cells(RowCount, StartCol), _ Cells(RowCount, LastColumn)) For Each cell In ColumnRange 'enter your code here ColLetter = ConvertColtoLetter(cell.Column) Range("E1").Value = ColLetter & RowCount Next cell StartCol = "A" Next RowCount End Sub Function ConvertColtoLetter _ (ColNumber As Integer) As String FirstBit = Int(ColNumber / 26) SecondBit = ColNumber Mod 26 If FirstBit = 0 Then ConvertColtoLetter = Chr(Asc("A") + SecondBit - 1) Else ConvertColtoLetter = _ Chr(Asc("A") + FirstBit - 1) Chr (Asc("A") + SecondBit - 1) End If End Function I have a range of cells like A1:D200 I want a macro that copy and paste A1 in E1 only then you run the macro again and it shows A2 in E1 so on till it reaches D200 and when i close excel and reopen it it should start from where it left off. Please Help thx. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200708/1 |
All times are GMT +1. The time now is 07:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com