Macro Help Needed

[Guest]

I want to write a macro that will start at cell A1 and go to A2 then to A3
and so on until it finds the first blank cell. At that point I want it to
stop at that blank cell so I can have it automatically write text in it. I
also need it to remember where it stopped so I can have it move over to
column B at the same row. I guess I just need to know how to make the active
cell move around with a macro to where I need it to be. Any help will be
greatly appreciated.

Dan

 

[Lonnie M.]

Hi, give this a try:
'#########################################################
Sub UpdateText
Dim CountData&
Dim myTextStringA$, myTextStringB$
CountData = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
Cells(CountData + 1, 1) = myTextStringA
Cells(CountData + 1, 2).Select
' Or if you want to insert another string...
' Cells(CountData + 1, 2) = myTextStringB
End Sub
'#########################################################
HTH--Lonnie M.

 

[Lonnie M.]

FYI:
The Convention Cells(X,Y)
'X' = row number
'Y' = column number
I.E. the cell A3 = Cells(3,1); B1 = Cells(1, 2)

 

[Guest]

Thanks, all that info helped a lot.

Now I need to be able to put an array formula in that cell and fill it
across. The formula needs to have the CountData variable in it. This is
what I have so far but it doesn't like it:

=Average(IF(B5:Bx>$H$2, IF(B5:Bx<$I$2, B5:Bx,""),"")) where "x" is CountData

Any ideas??

 

[Lonnie M.]

You probably have figured this out by now, but try something like
this...
Cells(CountData + 1, 1) = "=Average(IF(B5:Bx>$H$2, IF(B5:Bx<$I$2, B5:B"
& CountData & ",""""),"""")) "

HTH--Lonnie