Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),QuoteNotes, | Excel Worksheet Functions | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
find last cell in range with data, display cell address | Excel Worksheet Functions | |||
How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) |