Cell address
 

I am writing a macro in which I search a worksheet for a cell containing a
text string. I have managed to find the cell but then I want to performe
operations on all cells below that contains information. The problem is that
I cannot loop since I only manage to get the address in this form: $A$5 (or
other form that I do not know how to loop). I am wondering 1) do I have to
change the format of the cell address so that it is loopable (in the sense
that you can add a number to it). If so how? If not how do I solve the
problem? 2)How do I find the end of the list i.e. the first cell that
contains no information? I am very thankful for any assistance.

Cell address
 

Assuming that you have a range object of the found cell, you can get the row
and column of that object and use those in a loop

Set oCell = Find(...

For i = oCell.Row + 1 To 100
'etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anderssweden" wrote in message
...
I am writing a macro in which I search a worksheet for a cell containing a
text string. I have managed to find the cell but then I want to performe
operations on all cells below that contains information. The problem is
that
I cannot loop since I only manage to get the address in this form: $A$5
(or
other form that I do not know how to loop). I am wondering 1) do I have to
change the format of the cell address so that it is loopable (in the sense
that you can add a number to it). If so how? If not how do I solve the
problem? 2)How do I find the end of the list i.e. the first cell that
contains no information? I am very thankful for any assistance.

Cell address
 

Thank you very much for your fast reply. Unfortunately I am novice VB user
and I do not know how to get the range of the obejct. My code so far is:

Private Sub findValue2()
Dim rng As Range
Dim findVal As String
Set rng = Worksheets("Dimension").Cells.Find("qc", LookIn:=xlValues)

With this part of the code I find the cell. I do not know how to get the
range which I then can use for the loop. Would be very thankful (again) for
fast assistance!
banker


"Bob Phillips" skrev:

Assuming that you have a range object of the found cell, you can get the row
and column of that object and use those in a loop

Set oCell = Find(...

For i = oCell.Row + 1 To 100
'etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anderssweden" wrote in message
...
I am writing a macro in which I search a worksheet for a cell containing a
text string. I have managed to find the cell but then I want to performe
operations on all cells below that contains information. The problem is
that
I cannot loop since I only manage to get the address in this form: $A$5
(or
other form that I do not know how to loop). I am wondering 1) do I have to
change the format of the cell address so that it is loopable (in the sense
that you can add a number to it). If so how? If not how do I solve the
problem? 2)How do I find the end of the list i.e. the first cell that
contains no information? I am very thankful for any assistance.

Cell address
 

Sorry for disturbing but I think I found the answer:

Private Sub findValue2()
Dim rng As Range
Dim findVal As String
Set rng = Worksheets("Dimension").Cells.Find("qc", LookIn:=xlValues)
Worksheets("Indata").Range("K1") = rng
Worksheets("Indata").Range("K2") = rng.Row
Worksheets("Indata").Range("K3") = rng.Column
--
banker


"Bob Phillips" skrev:

Assuming that you have a range object of the found cell, you can get the row
and column of that object and use those in a loop

Set oCell = Find(...

For i = oCell.Row + 1 To 100
'etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anderssweden" wrote in message
...
I am writing a macro in which I search a worksheet for a cell containing a
text string. I have managed to find the cell but then I want to performe
operations on all cells below that contains information. The problem is
that
I cannot loop since I only manage to get the address in this form: $A$5
(or
other form that I do not know how to loop). I am wondering 1) do I have to
change the format of the cell address so that it is loopable (in the sense
that you can add a number to it). If so how? If not how do I solve the
problem? 2)How do I find the end of the list i.e. the first cell that
contains no information? I am very thankful for any assistance.

Cell address
 

You are already doing it. rng is getting the range <g

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anderssweden" wrote in message
...
Thank you very much for your fast reply. Unfortunately I am novice VB user
and I do not know how to get the range of the obejct. My code so far is:

Private Sub findValue2()
Dim rng As Range
Dim findVal As String
Set rng = Worksheets("Dimension").Cells.Find("qc", LookIn:=xlValues)

With this part of the code I find the cell. I do not know how to get the
range which I then can use for the loop. Would be very thankful (again)
for
fast assistance!
banker


"Bob Phillips" skrev:

Assuming that you have a range object of the found cell, you can get the
row
and column of that object and use those in a loop

Set oCell = Find(...

For i = oCell.Row + 1 To 100
'etc.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"anderssweden" wrote in message
...
I am writing a macro in which I search a worksheet for a cell
containing a
text string. I have managed to find the cell but then I want to
performe
operations on all cells below that contains information. The problem
is
that
I cannot loop since I only manage to get the address in this form:
$A$5
(or
other form that I do not know how to loop). I am wondering 1) do I
have to
change the format of the cell address so that it is loopable (in the
sense
that you can add a number to it). If so how? If not how do I solve the
problem? 2)How do I find the end of the list i.e. the first cell that
contains no information? I am very thankful for any assistance.