Macro editing



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

Bob Phillips

Do it manually with the macro recorder turned on, this will generate the
basic code. Then just edit it for flexibility.



(there's no email, no snail mail, but somewhere should be gmail in my addy)


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.


to get the last row or column you need to use the END method. there is two
form of the method, one for rows and one for columns

LastRow = cells(Rows.count,"A").end(xlup).row

Row.count = 64536 the last row on the worksheet. XLUP tells excel to move
up the worksheet until it finds a non empty cell. You can use any colymn in
place of "A"

LastColumn = cells(1,Columns.count).end(xltoleft).column

Columns.count = 256 the lat column in the worksheet. XLTOLEFT tells excel
to go left until it finds a non-empty cell

256 and 64536 are the limits in excel 2003. Ecel 2007 has an extended range.


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)
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)
ConvertColtoLetter = _
Chr(Asc("A") + FirstBit - 1)
Chr (Asc("A") + SecondBit - 1)
End If

End Function

saman110 via said:
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


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question