Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
copy/paste won't refer to the appropriate range of cells | Excel Discussion (Misc queries) | |||
How do I copy the contents of a range of text cells and paste into one cell? | Excel Discussion (Misc queries) | |||
Macro to copy, paste in a range and then delete | Excel Discussion (Misc queries) | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) |