Editing a Range from a value

[lwaymex]

OK this is what i'm trying to do in a VBA macro:

I have a value=4 on my cell B1.
What i'm trying to do is putting this value inside my formula:
COUNTA(A1:A4)

so, if i change my B1 value the formula should change:
B1=5 --> COUNTA(A1:A5)
B1=9 --> COUNTA(A1:A9)
B1=5000 --> COUNTA(A1:A5000)

COULD BE POSSIBLE?


<?xml:namespace prefix = v ns = "urn:schemas-microsoft-com:vml" /><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 352.5pt; HEIGHT: 170.25pt; mso-wrap-style: square" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="" src="file:///C:\DOCUME~1\ns517737\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png">
<v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" oreferrelative="t" o:spt="75" coordsize="21600,21600"><v:stroke joinstyle="miter"></v:stroke><v:formulas><v:f eqn="if lineDrawn pixelLineWidth 0"></v:f><v:f eqn="sum @0 1 0"></v:f><v:f eqn="sum 0 0 @1"></v:f><v:f eqn="prod @2 1 2"></v:f><v:f eqn="prod @3 21600 pixelWidth"></v:f><v:f eqn="prod @3 21600 pixelHeight"></v:f><v:f eqn="sum @0 0 1"></v:f><v:f eqn="prod @6 1 2"></v:f><v:f eqn="prod @7 21600 pixelWidth"></v:f><v:f eqn="sum @8 21600 0"></v:f><v:f eqn="prod @7 21600 pixelHeight"></v:f><v:f eqn="sum @10 21600 0"></v:f></v:formulas><vath o:connecttype="rect" gradientshapeok="t" o:extrusionok="f"></vath><o:lock aspectratio="t" v:ext="edit"></o:lock></v:shapetype><v:shape id=Picture_x0020_1 style="VISIBILITY: visible; WIDTH: 352.5pt; HEIGHT: 170.25pt; mso-wrap-style: square" type="#_x0000_t75" o:spid="_x0000_i1025"><v:imagedata o:title="" src="file:///C:\DOCUME~1\ns517737\LOCALS~1\Temp\msohtmlclip1\01\clip_image001.png"></v:imagedata></v:shape>
</v:imagedata></v:shape>

 

[xenou]

Without vba is very easy:
=CountA(Offset(A1,0,0,B1,1))

 

[Special-K99]

This works, may not be best method (fairly new to VBA meself)

Range("D1").Formula = "=COUNT(A1:A" & Range("B1").Value & ")"