Macro editing
Good point!!!
Not bad idea also to use a loop.
Have a look at this post and change the code to your needs:
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.
"Skeeter" wrote:
Thanks Bob for your quick response! I did run the Macro manually and it does
essentially what I want it to do except that when I run the Macro the second
time it just repeats and enters the same name and values again in the same
worksheet at the same location. I want the Macro to continue down my list
and insert each new name and values one after the other. My problem is I
don't know how tot tell the Macro in the editing window of VBE how to perform
this task.
"Tim" wrote:
Hi,
And Run it as much as you want
Regards,
Tim
"Bob Phillips" wrote:
Do it manually with the macro recorder turned on, this will generate the
basic code. Then just edit it for flexibility.
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my addy)
"Skeeter" wrote in message
...
I am trying to create a Macro where I select a name from a filtered list I
created. I need the Macro to select the next row or name in the filtered
list each time I activate my Macro. This filtered list has data attached
to
the names and the filter selects all the numbers I need to be pasted into
a
worksheet titled "Totals".
|