Need help with adding variables to cell addresses

[Matt]

Hi There,

I ran into more problems ...

Now I have a macro which determines the start cell of a range of cells
with this function:

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

in cell D2 it will put like $B$17.

- How can i make it so it doesnt put $ in there but just B17?

- I have a second function:

ActiveCell.FormulaR1C1 = "=count(R5C2:R65000C2)

which determines how many used cell sare in the column .
and puts the value in lets say D3


Now I need to find the address of the last cell which is B(17+D3).

But I cant get it to work It wont add up and I tried Left function
to seperate values but it will only worh on a string.

Any help appreciated

Matt

 

[Guest]

Range("D2").Value = replace(ActiveCell.Address,"$","")

 

[Tom Ogilvy]

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


Range( "B" & 17 + Range("D3).Value)

 

[Bernie Deitrick]

Matt,

To put B17 into cell D2:
Range("D2").Value = ActiveCell.Address(False, False)

Address of the last cell in column B is (using your method)
myAdd = Range("B" & 17 + Range("D3").Value).Address(False, False)
MsgBox myAdd

But this is better, since it isn't dependent on lack of blanks, or the use of a particular starting
row:
myAdd = Cells(Rows.Count, 2).End(xlUp).Address(False, False)

But there isn't any need to do what you are describing. It would be better to describe what it is
that you want to do, and get code that will teach you something...

HTH,
Bernie
MS Excel MVP

 

[Matt]

Hi Bernie

I have column B with values. In which line they start and end is
different each time the macro runs. I wrote a function that finds the
first used cell:

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

Loop


I then copy the value of the active cell, which is the first cell that
contains data in lets say cell D1.

I then determine the last full cell wit this (I know thats brutal):

Do
ActiveCell.Offset(1, 0).Select
If IsEmpty(ActiveCell) Then
Exit Do
End If
Loop

and put the active cell in D2.

So I end up with my start and end for my data range in D1 and D2. It
works well but is a silly method and is slow.

I relaized that I can use count() to find the amount of cells with data
and "simply" add the result to D1. If I put the result in D2 i have
the same as before with the "crawler" but faster. I cant get it to
work though... I also couldnt get Tom's suggestion to work

Take in mind I am no programmer and started this a week ago. This
project snealed up on me...

Matt

 

[Bernie Deitrick]

Matt,

The important thing is: What do you do with the addresses once they are in cells D1 and D2?

And are you concerned with finding the first date in column B? Or just the first filled cell? And is
there always at least one blank cell at the top of column B?

HTH,
Bernie
MS Excel MVP

 

[Tom Ogilvy]

set rng = Columns(2).specialCells(xlconstants)
Range("D1").Value = rng(1).Address(0,0)
Range("D2").Value = rng(rng.count).Address(0,0)
msgbox "Range: " & rng.Address(0,0) & _
"FirstCell: " & rng(1).Address(0,0) & _
"LastCell: " & rng(rng.count).Address(0,0)

 

[Matt]

Once I have start and end of the range isolated i use it to draw a
chart:

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

'Ennd = Ennd + Range("G1").Value


'Range("D1") = Begn
'Range("D2") = Ennd


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

column B contains a formula which will make the cell either blank or
put in a date. the first 5 rows are always empty. First filled cell
is equal to first date. But I found the function that lloks for dats
so i used that ....



Matt

P.S.

Thanks Tom will test your suggestion now.

 

[Matt]

sweet! Thanks Tom! It looks so easy when you do it ... my crawler took
a long time to crawl throught the cells .. not talking about how silly
my method was ...

Matt

 

[Matt]

we have a problem ... the function counts the cells that contained a
formula.... I tried to copy them and to paste special values but
although the cells are now empty they are counted in the function ...

How can it be changed so it only counts cells which contain an actual
value or date ?

Matt

 

[Bernie Deitrick]

Matt,

Run these two macros, and determine which one (if either) selects the correct cells:

Sub Macro1()
Range("B:B").SpecialCells(xlCellTypeConstants, 1).Select
End Sub

Sub Macro2()
Range("B:B").SpecialCells(xlCellTypeFormulas, 1).Select
End Sub

HTH,
Bernie
MS Excel MVP

 

[Matt]

Macro 2 did. macro 1 had an error ...

Matt

 

[Tom Ogilvy]

Then
set rng = Columns(2).specialCells(xlFormulas)
Range("D1").Value = rng(1).Address(0,0)
Range("D2").Value = rng(rng.count).Address(0,0)
msgbox "Range: " & rng.Address(0,0) & _
"FirstCell: " & rng(1).Address(0,0) & _
"LastCell: " & rng(rng.count).Address(0,0)

 

[Bernie Deitrick]

Matt,

Try this. Replace:

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range(Begn,
Ennd), PlotBy:= _

with

ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("B:B") _
..SpecialCells(xlCellTypeFormulas, 1), PlotBy:= _


HTH,
Bernie
MS Excel MVP

 

[Matt]

Tom, Bernie

I think we got it! Some tweaks to the formatting and it should be done