How to Join Member's Area Private Library Search Today's Topics p Login
Main Forums Discussion Tech Talk Mature Content Archives
   Nav Win
 Tech Talk
 Geek Stuff
 Excel / VBA - Defining Address Location
 1 2 3 4 5 6 7
Follow us on Facebook

 Moderated by: Christopher   (Admins )

 
User Options
Format for Better Printing EMail to a Friend Not Available
Admin Print Send ECard
Passions in Poetry

Excel / VBA - Defining Address Location

 Post A Reply Post New Topic   Go to the Next Oldest/Previous Topic Return to Topic Page Go to the Next Newest Topic 
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


0 posted 03-08-2005 02:46 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher


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
Deputy Moderator 10 ToursDeputy Moderator 5 Tours
Moderator
Member Seraphic
since 01-18-2000
Posts 24152
with you


1 posted 03-08-2005 03:37 PM       View Profile for SEA   Email SEA   Edit/Delete Message      Find Poems  View IP for SEA

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 05-19-99
Posts 9708
Michigan, US


2 posted 03-08-2005 04:24 PM       View Profile for Ron   Email Ron   Edit/Delete Message      Find Poems   Click to visit Ron's Home Page   View IP for Ron

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 08-02-99
Posts 9130
Purgatorial Incarceration


3 posted 03-08-2005 05:26 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

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 02-02-2000
Posts 28839


4 posted 03-08-2005 07:19 PM       View Profile for serenity blaze   Email serenity blaze   Edit/Delete Message      Find Poems  View IP for serenity blaze

What?

No "mathy stuff"?



*pout*
Christopher
Moderator
Member Rara Avis
since 08-02-99
Posts 9130
Purgatorial Incarceration


5 posted 03-08-2005 07:48 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

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 08-02-99
Posts 9130
Purgatorial Incarceration


6 posted 03-08-2005 07:54 PM       View Profile for Christopher   Email Christopher   Edit/Delete Message      Find Poems  View IP for Christopher

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 02-02-2000
Posts 28839


7 posted 03-08-2005 08:01 PM       View Profile for serenity blaze   Email serenity blaze   Edit/Delete Message      Find Poems  View IP for serenity blaze

*curtsy*

 
 Post A Reply Post New Topic   Go to the Next Oldest/Previous Topic Return to Topic Page Go to the Next Newest Topic 
All times are ET (US) Top
  User Options
>> Tech Talk >> Geek Stuff >> Excel / VBA - Defining Address Location Format for Better Printing EMail to a Friend Not Available
Print Send ECard

 

pipTalk Home Page | Main Poetry Forums

How to Join | Member's Area / Help | Private Library | Search | Contact Us | Today's Topics | Login
Discussion | Tech Talk | Archives | Sanctuary



© Passions in Poetry and netpoets.com 1998-2013
All Poetry and Prose is copyrighted by the individual authors