Excel / VBA - Defining Address Location

How to Join	Member's Area	Private Library	Search	Sanctuary		Login
Main Forums	Discussion	Tech Talk	Mature Content	Archives	A home without a wall, Our haven without ends, A circle we all call Our Family of Friends!

Christopher

Moderator

Member Rara Avis

since 1999-08-02

Posts 8296
Purgatorial Incarceration

0 posted 2005-03-08 02:46 PM

I'm working on a program in excel which requires a specific row in which to start looking for something to do stuff with/to. The problem is that the row changes depending on several variables. At the moment I have an extensive "library" of where it will be if this variable and this variable are such-and-such and if this variable and this varaiable are this-and-that... but I need to add more variables.

I can name a cell where it starts and locate it by the name. That's not a problem. The problem is that once it finds this row, it needs to be able to sequentially run down the following rows... which doesn't work unless I can define inside the program exactly what row it starts in. Getting the name will only help if I can have the actual row number returned somehow.

I've looked through the book, looked on the net and can't seem to find a way. Am I just stuck or is there a way to do this?

Thanks

Chris

egowhores.com - really love yourself.

SEA

Moderator

Member Seraphic

since 2000-01-18

Posts 22676
with you

1 posted 2005-03-08 03:37 PM

I have no idea what you are talking about...but you sure do look good saying it Kissy...

hehehehe

Ron

Administrator

Member Rara Avis

since 1999-05-19

Posts 8669
Michigan, US

2 posted 2005-03-08 04:24 PM

code:


Dim AllNames as Names
Dim NameCount as Long

Set AllNames = ActiveWorkbook.Names
For NameCount = 1 to AllNames.Count
	If AllNames(NameCount).Name = "YourNamedCell" Then
		MsgBox (AllNames(NameCount).RefersToRange.Address)
	End If
Next

That should put you on the right path, Chris ...

Christopher

Moderator

Member Rara Avis

since 1999-08-02

Posts 8296
Purgatorial Incarceration

3 posted 2005-03-08 05:26 PM

Thanks, Ron.

This looks close to what I want. I'll give it a shot!

egowhores.com - really love yourself.

serenity blaze
Member Empyrean

since 2000-02-02

Posts 27738

4 posted 2005-03-08 07:19 PM

What?

No "mathy stuff"?

*pout*

Christopher

Moderator

Member Rara Avis

since 1999-08-02

Posts 8296
Purgatorial Incarceration

5 posted 2005-03-08 07:48 PM

If you insist, Karen...

code:

Do
    testCell = Cells(row_unq, col_unq)
    testCell2 = Cells(row_trf, col_trf)
    
    'Tests if this is a row with a plan in it
    If testCell2 = "False" Then
    
        ba = Cells(row_act, col_act) 'Define row and column for actual price
        sc = Cells(row_sup, col_sup) 'Define row and column for supplier cost
        sp = Cells(row_spt, col_spt) 'Define row and column for specialty items
                
        Sum = ((ba - sc - sp) - hd * (sc + sp)) / (hd + 1) 

        Cells(row_con, col_con) = Sum 'Write contingency cost into cell
        
        If ba > 0 Then
            
            countPlans = countPlans + 1
            
        End If
               
        
    End If
    
    'The following increases row count to go to next row
    row_act = row_act + 1
    row_sup = row_sup + 1
    row_spt = row_spt + 1
    row_trf = row_trf + 1
    row_unq = row_unq + 1
    row_con = row_con + 1
        
Loop While testCell = "~xx~"

If you'll notice, the above formula has been seen in a similar form before, in this very same forum.

Christopher

Moderator

Member Rara Avis

since 1999-08-02

Posts 8296
Purgatorial Incarceration

6 posted 2005-03-08 07:54 PM

And Ron, that works perfectly. All I had to change was "RefersToRange.Address" to "RefersToRange.Row" - the VITAL part being the knowledge of "RefersToRange" in the first place.

Again, thank you. This will help me clean up my program considerably!

serenity blaze
Member Empyrean

since 2000-02-02

Posts 27738

7 posted 2005-03-08 08:01 PM

*curtsy*

	⇧ top of page ⇧
All times are ET (US). All dates are in Year-Month-Day format.