Repeat Macro until Empty Cell Reached

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:

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 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...552cb4061260dd

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

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)

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...552cb4061260dd
>
> 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

Good point ...

ok this is what the data looks like

Readings: 8803

A B C D
9/15/2005 9:06 22.68802
9/15/2005 9:08 23.0421
9/15/2005 9:10 23.75181
9/15/2005 9:12 23.75181 9/15/2005 9:12 23.75181
9/15/2005 9:14 23.75181 9/15/2005 9:14 23.75181
9/15/2005 9:16 23.75181 9/15/2005 9:16 23.75181
9/15/2005 9:18 23.75181 9/15/2005 9:18 23.75181
9/15/2005 9:20 23.39668 9/15/2005 9:20 23.39668
9/15/2005 9:22 23.0421 9/15/2005 9:22 23.0421
9/15/2005 9:24 23.0421 9/15/2005 9:24 23.0421
9/15/2005 9:26 23.0421 9/15/2005 9:26 23.0421
9/15/2005 9:28 23.0421 9/15/2005 9:28 23.0421
9/15/2005 9:30 23.0421
9/15/2005 9:32 23.0421
9/15/2005 9:34 23.0421
9/15/2005 9:36 23.0421
9/15/2005 9:38 23.0421
9/15/2005 9:40 23.0421
9/15/2005 9:42 23.0421
9/15/2005 9:44 23.0421


Column A has the date, B the time and the macro copies a formula in C
and D which if the date is in a range that was queried it copies the
date in C and temp in D. This is then used to find the highest and
lowest temperature...

Now I have to also make a chart over the cells in column C and D to
plot the temperature. I know the date where the range starts and ends
but NOT the cell. Thats why i used your macro to search the columns to
find the start and end. It works EXCEPT. the cursor needs to be placed
at the first cell with data maually and its very slow and I dont know
how to make a chart.

I can get it to write the start and end cell in lets say cells A1 and
A2... which would have the value i.e. $C$23 and $C$422.

Matt

If you used column C, then columns A:D would be part of that currentregion.

But you could just check column C:

Option Explicit
Sub testme()

Dim myRng As Range
Dim TopCell As Range
Dim BotCell As Range
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
Set BotCell = .Cells(.Rows.Count, "C").End(xlUp)
Set TopCell = BotCell.End(xlUp)

Set myRng = .Range(TopCell, BotCell).Resize(, 2) 'both columns

MsgBox myRng.Address
End With

End Sub


Matt wrote:
>
> Good point ...
>
> ok this is what the data looks like
>
> Readings: 8803
>
> A B C D
> 9/15/2005 9:06 22.68802
> 9/15/2005 9:08 23.0421
> 9/15/2005 9:10 23.75181
> 9/15/2005 9:12 23.75181 9/15/2005 9:12 23.75181
> 9/15/2005 9:14 23.75181 9/15/2005 9:14 23.75181
> 9/15/2005 9:16 23.75181 9/15/2005 9:16 23.75181
> 9/15/2005 9:18 23.75181 9/15/2005 9:18 23.75181
> 9/15/2005 9:20 23.39668 9/15/2005 9:20 23.39668
> 9/15/2005 9:22 23.0421 9/15/2005 9:22 23.0421
> 9/15/2005 9:24 23.0421 9/15/2005 9:24 23.0421
> 9/15/2005 9:26 23.0421 9/15/2005 9:26 23.0421
> 9/15/2005 9:28 23.0421 9/15/2005 9:28 23.0421
> 9/15/2005 9:30 23.0421
> 9/15/2005 9:32 23.0421
> 9/15/2005 9:34 23.0421
> 9/15/2005 9:36 23.0421
> 9/15/2005 9:38 23.0421
> 9/15/2005 9:40 23.0421
> 9/15/2005 9:42 23.0421
> 9/15/2005 9:44 23.0421
>
> Column A has the date, B the time and the macro copies a formula in C
> and D which if the date is in a range that was queried it copies the
> date in C and temp in D. This is then used to find the highest and
> lowest temperature...
>
> Now I have to also make a chart over the cells in column C and D to
> plot the temperature. I know the date where the range starts and ends
> but NOT the cell. Thats why i used your macro to search the columns to
> find the start and end. It works EXCEPT. the cursor needs to be placed
> at the first cell with data maually and its very slow and I dont know
> how to make a chart.
>
> I can get it to write the start and end cell in lets say cells A1 and
> A2... which would have the value i.e. $C$23 and $C$422.
>
> Matt

--

Dave Peterson

Dave thanks for your help

I got this (see below) to work (dont ask how)


What it does is, it crawls through my column with dates and will write
the address of the last in cell D1 and the first in D1.

Now I "simply" need to run a chart from the range that is in these
cells .. I cant figure out how though....

I need to turn the VALUE of cell D1 into a variable and the same with
D2 and then chart (value(D2):value(D1)).

Since I have no clue about programing I dont know how...

If I get this done I am done my project

Matt




Sub ED()



' find first cell with a date amongst EMPTY cells then stop Do

Do
ActiveCell.Offset(1, 0).Select
If Not IsDate(ActiveCell) Then Else GoTo Line1

Loop





Line1:

'This is the first full cell

Range("D2").Value = ActiveCell.Address

'Find LAST full cell DOWN

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

ActiveCell.Value = ActiveCell.Address


ActiveCell.Offset(-1, 0).Select
Range("D1").Value = ActiveCell.Address
ActiveCell.Offset(1, 0).Select


' obsolete logic

'Find First full cell UP

'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 = ActiveCell.Address
' Range("D2").Value = ActiveCell.Address

End Sub

I'd say record a macro that creates the chart you like. Then look at the code
and try to change the code to use the values you know--instead of what was
selected.

But I still don't understand what you're doing...

Matt wrote:
>
> Dave thanks for your help
>
> I got this (see below) to work (dont ask how)
>
> What it does is, it crawls through my column with dates and will write
> the address of the last in cell D1 and the first in D1.
>
> Now I "simply" need to run a chart from the range that is in these
> cells .. I cant figure out how though....
>
> I need to turn the VALUE of cell D1 into a variable and the same with
> D2 and then chart (value(D2):value(D1)).
>
> Since I have no clue about programing I dont know how...
>
> If I get this done I am done my project
>
> Matt
>
> Sub ED()
>
> ' find first cell with a date amongst EMPTY cells then stop Do
>
> Do
> ActiveCell.Offset(1, 0).Select
> If Not IsDate(ActiveCell) Then Else GoTo Line1
>
> Loop
>
> Line1:
>
> 'This is the first full cell
>
> Range("D2").Value = ActiveCell.Address
>
> 'Find LAST full cell DOWN
>
> Do
> 'ActiveCell.Value = Mid(ActiveCell.Value, 6)
> ActiveCell.Offset(1, 0).Select
> If IsEmpty(ActiveCell) Then
> Exit Do
> End If
> Loop
>
> ActiveCell.Value = ActiveCell.Address
>
> ActiveCell.Offset(-1, 0).Select
> Range("D1").Value = ActiveCell.Address
> ActiveCell.Offset(1, 0).Select
>
> ' obsolete logic
>
> 'Find First full cell UP
>
> '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 = ActiveCell.Address
> ' Range("D2").Value = ActiveCell.Address
>
> End Sub

--

Dave Peterson

Dave Peterson wrote:
> I'd say record a macro that creates the chart you like. Then look at the code
> and try to change the code to use the values you know--instead of what was
> selected.
>
> But I still don't understand what you're doing...

I have to create a chart for a range of cells which changes every time
the macro runs...

If I record a macro it will only work for the range I selected....

I have the macro (see below) that puts in the start cell for my range
in cell D2 and the end in D1.

So my cells look like that:

D1: $A$5
D2: $A$8808

Now the macro has to make a chart from cell A5 to A8808 (and not from
D1 to D2)

Next time it has different values in D1 and D2, thats why I need
variables.

I was able to put the value of D1 and D2 into variables but I cant make
a chart from variables as addresses ...

If I get this done will stop pestering you

Matt












>
> Matt wrote:
> >
> > Dave thanks for your help
> >
> > I got this (see below) to work (dont ask how)
> >
> > What it does is, it crawls through my column with dates and will write
> > the address of the last in cell D1 and the first in D1.
> >
> > Now I "simply" need to run a chart from the range that is in these
> > cells .. I cant figure out how though....
> >
> > I need to turn the VALUE of cell D1 into a variable and the same with
> > D2 and then chart (value(D2):value(D1)).
> >
> > Since I have no clue about programing I dont know how...
> >
> > If I get this done I am done my project
> >
> > Matt
> >
> > Sub ED()
> >
> > ' find first cell with a date amongst EMPTY cells then stop Do
> >
> > Do
> > ActiveCell.Offset(1, 0).Select
> > If Not IsDate(ActiveCell) Then Else GoTo Line1
> >
> > Loop
> >
> > Line1:
> >
> > 'This is the first full cell
> >
> > Range("D2").Value = ActiveCell.Address
> >
> > 'Find LAST full cell DOWN
> >
> > Do
> > 'ActiveCell.Value = Mid(ActiveCell.Value, 6)
> > ActiveCell.Offset(1, 0).Select
> > If IsEmpty(ActiveCell) Then
> > Exit Do
> > End If
> > Loop
> >
> > ActiveCell.Value = ActiveCell.Address
> >
> > ActiveCell.Offset(-1, 0).Select
> > Range("D1").Value = ActiveCell.Address
> > ActiveCell.Offset(1, 0).Select
> >
> > ' obsolete logic
> >
> > 'Find First full cell UP
> >
> > '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 = ActiveCell.Address
> > ' Range("D2").Value = ActiveCell.Address
> >
> > End Sub
>
> --
>
> Dave Peterson

Exactly--your macro should be able to be modified to use those addresses you've
saved.

But there are lots of charts--guessing what you want would be impossible.

Matt wrote:
>
> Dave Peterson wrote:
> > I'd say record a macro that creates the chart you like. Then look at the code
> > and try to change the code to use the values you know--instead of what was
> > selected.
> >
> > But I still don't understand what you're doing...
>
> I have to create a chart for a range of cells which changes every time
> the macro runs...
>
> If I record a macro it will only work for the range I selected....
>
> I have the macro (see below) that puts in the start cell for my range
> in cell D2 and the end in D1.
>
> So my cells look like that:
>
> D1: $A$5
> D2: $A$8808
>
> Now the macro has to make a chart from cell A5 to A8808 (and not from
> D1 to D2)
>
> Next time it has different values in D1 and D2, thats why I need
> variables.
>
> I was able to put the value of D1 and D2 into variables but I cant make
> a chart from variables as addresses ...
>
> If I get this done will stop pestering you
>
> Matt
>
> >
> > Matt wrote:
> > >
> > > Dave thanks for your help
> > >
> > > I got this (see below) to work (dont ask how)
> > >
> > > What it does is, it crawls through my column with dates and will write
> > > the address of the last in cell D1 and the first in D1.
> > >
> > > Now I "simply" need to run a chart from the range that is in these
> > > cells .. I cant figure out how though....
> > >
> > > I need to turn the VALUE of cell D1 into a variable and the same with
> > > D2 and then chart (value(D2):value(D1)).
> > >
> > > Since I have no clue about programing I dont know how...
> > >
> > > If I get this done I am done my project
> > >
> > > Matt
> > >
> > > Sub ED()
> > >
> > > ' find first cell with a date amongst EMPTY cells then stop Do
> > >
> > > Do
> > > ActiveCell.Offset(1, 0).Select
> > > If Not IsDate(ActiveCell) Then Else GoTo Line1
> > >
> > > Loop
> > >
> > > Line1:
> > >
> > > 'This is the first full cell
> > >
> > > Range("D2").Value = ActiveCell.Address
> > >
> > > 'Find LAST full cell DOWN
> > >
> > > Do
> > > 'ActiveCell.Value = Mid(ActiveCell.Value, 6)
> > > ActiveCell.Offset(1, 0).Select
> > > If IsEmpty(ActiveCell) Then
> > > Exit Do
> > > End If
> > > Loop
> > >
> > > ActiveCell.Value = ActiveCell.Address
> > >
> > > ActiveCell.Offset(-1, 0).Select
> > > Range("D1").Value = ActiveCell.Address
> > > ActiveCell.Offset(1, 0).Select
> > >
> > > ' obsolete logic
> > >
> > > 'Find First full cell UP
> > >
> > > '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 = ActiveCell.Address
> > > ' Range("D2").Value = ActiveCell.Address
> > >
> > > End Sub
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson

Dave Peterson wrote:
> Exactly--your macro should be able to be modified to use those addresses you've
> saved.
>
> But there are lots of charts--guessing what you want would be impossible.



Any chart will do ... I can modify it until it fits ...

My problem is to generate ANY chart from variables...

A macro uses this command to insert a chart:

ActiveChart.SetSourceData Source:=Sheets("Data").Range("D1:D8"),
PlotBy:= _
xlColumns

I need to know what do I put where it says "Range("D1:D8")" so that it
will take the value of cell D2 and D1 and put the values in as range.
Because i have my range start and end in these cells D1 and D2 thats
all i need... dont worry about details like type of chart and such .. I
am sure i can get the looks to work but I cant figure out the command
to put in the chart in the first place

Matt

thats what I tried from some info from older posts here:

But it doesnt work



Sub cit()


Begn = Range("D2").Value
Ennd = Range("D1").Value



Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets("Data").Range(Begn &
Ennd), PlotBy:= _
xlColumns
ActiveChart.Location Where:=xlLocationAsObject, Name:="Data"
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "A70"
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

I got it ... it needed a comma instead of colon ...

ctiveChart.SetSourceData Source:=Sheets("Data").Range(Begn, Ennd),
PlotBy:= _
xlColumns

Now it works

Thanks for all the help that got me to this point!

Matt

I'm not sure how much I helped--you did lots of work by yourself--but that's a
good way to learn!

And glad you got it working, too!

Matt wrote:
>
> I got it ... it needed a comma instead of colon ...
>
> ctiveChart.SetSourceData Source:=Sheets("Data").Range(Begn, Ennd),
> PlotBy:= _
> xlColumns
>
> Now it works
>
> Thanks for all the help that got me to this point!
>
> Matt

--

Dave Peterson

Dave Peterson wrote:
> I'm not sure how much I helped--you did lots of work by yourself--but that's a
> good way to learn!
>
> And glad you got it working, too!

Although I dont understand much of the help people give, you helped a
great deal


Of course now I realise that the formatting of the newly created chart
isnt as easy as I thought ... I recorded a macro and it references
chart 11 but it should use activechart or something ....

I will let this rest til next week, the main work seems to be done...

Thanks again!

Matt