How do I repeat the following macro until it reaches an empty cell?

ActiveCell.Offset(0, 0).Select
Application.SendKeys ("{F2}")
Application.SendKeys ("{Home}")
Application.SendKeys ("{Del 5}")
Application.SendKeys ("{ENTER}")

option explicit
sub testme()
do
activecell.value = mid(activecell.value,6)
activecell.offset(1,0).select
if isempty(activecell) then
exit do
end if
loop
end sub




clpncsg wrote:

How do I repeat the following macro until it reaches an empty cell?

ActiveCell.Offset(0, 0).Select
Application.SendKeys ("{F2}")
Application.SendKeys ("{Home}")
Application.SendKeys ("{Del 5}")
Application.SendKeys ("{ENTER}")



--

Dave Peterson

Dave:

Thank you. This solution worked perfect.

Cindy

"Dave Peterson" wrote:

option explicit
sub testme()
do
activecell.value = mid(activecell.value,6)
activecell.offset(1,0).select
if isempty(activecell) then
exit do
end if
loop
end sub




clpncsg wrote:

How do I repeat the following macro until it reaches an empty cell?

ActiveCell.Offset(0, 0).Select
Application.SendKeys ("{F2}")
Application.SendKeys ("{Home}")
Application.SendKeys ("{Del 5}")
Application.SendKeys ("{ENTER}")



--

Dave Peterson

I modified it like this:


Sub ED()
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop

ActiveCell.Value = "END"
ActiveCell.Offset(0, 1).Select
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(-1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = "START"

End Sub


Now what this does is it goes down until it finds an empty cell, then
goes right and then up until it finds an empty cell and left again.

Now this lets me find my range of used cells.

How can I now make a function which selects all cells between the cell
that say START and END and make a graph from that?

I really know nothing about VBA :( need help.

Matt

I'm confused about what you want to do.

Usually I know the cell that I want to start with--I don't have to rely on
starting with the activecell. And going right and up and left until you find an
empty cell sounds like you could use a different approach.

And you're adjusting some cells (by trimming those 5 leading characters), but
not other cells. Is that really what you want?

For instance...

option explicit
sub Ed2()
dim myCell as range
dim myRng as range

set myrng = activecell.currentregion

for each mycell in myrng.cells
mycell.value = mid(mycell.value,6)
next mycell

'now you can use myRng as the basis for your chart, well, maybe...

end Sub

So you have a few questions to answer -- maybe just describe what you really
want.

And if you record a macro when you create the chart you like, you could post
that code and that code could be adjusted to use the range that meets your
description.



Matt wrote:

I modified it like this:

Sub ED()
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop

ActiveCell.Value = "END"
ActiveCell.Offset(0, 1).Select
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(-1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = "START"

End Sub

Now what this does is it goes down until it finds an empty cell, then
goes right and then up until it finds an empty cell and left again.

Now this lets me find my range of used cells.

How can I now make a function which selects all cells between the cell
that say START and END and make a graph from that?

I really know nothing about VBA :( need help.

Matt

--

Dave Peterson

here is a description of the original problem:

http://groups.google.ca/group/micros...2cb406126 0dd

the 3rd last post has a link to a photo ..

I cant figure out how to select the rows with the date directly. BUt
your macro that goes through the rows selects them ...

I found a function which will put the address of the first cell in A1
and the last cell in B1, so these cells would contain i.e. $B$34
and $B$412

The macro has to search 65000 lines unless I can select the end point
better ... I know how many data points I have so I could limit the
search to them. I just dont know how all that works in VBA ... also I
dont know how to address celles relatively,

for example I need cell Ax where x is the number in a cell ....

Matt

Since you've separated your data into that area, it sure looks like you could
use:

range("somecellinthatarea").currentregion

And this is pretty much a plain text newsgroup. Many people will just skip by
your post if it contains attachments--pictures, too.

And if you add steps to click on links to download pictures, others will just
figure it's not worth their time.

I'd stick to plain text eplanations if I were posting.

Matt wrote:

here is a description of the original problem:

http://groups.google.ca/group/micros...2cb406126 0dd

the 3rd last post has a link to a photo ..

I cant figure out how to select the rows with the date directly. BUt
your macro that goes through the rows selects them ...

I found a function which will put the address of the first cell in A1
and the last cell in B1, so these cells would contain i.e. $B$34
and $B$412

The macro has to search 65000 lines unless I can select the end point
better ... I know how many data points I have so I could limit the
search to them. I just dont know how all that works in VBA ... also I
dont know how to address celles relatively,

for example I need cell Ax where x is the number in a cell ....

Matt

--

Dave Peterson

I dont need them trimmed but I have not much clue about VBA so i just
took your code and added some more code.. I dont know what the
functions do and whats their syntax .. the excel help hasnt been much
help so far

I think I dont need that line: mycell.value = mid(mycell.value,6)

I modified it like this:


Sub ED()
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop

ActiveCell.Value = "END"
ActiveCell.Offset(0, 1).Select
Do
ActiveCell.Value = Mid(ActiveCell.Value, 6)
ActiveCell.Offset(-1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop
ActiveCell.Offset(0, -1).Select
ActiveCell.Value = "START"

End Sub


Now what this does is it goes down until it finds an empty cell, then
goes right and then up until it finds an empty cell and left again.

Now this lets me find my range of used cells.

How can I now make a function which selects all cells between the cell
that say START and END and make a graph from that?

I really know nothing about VBA :( need help.

Matt