Solved

Range Value from formula

Posted on 2009-03-31
10
378 Views
Last Modified: 2012-05-06
HI,

I'm trying to get this value from this:
Range("N2:N" & Cells(65536, "X").End(X1Up).Row)

The "N" column contains a formula: =(M2-L2)*1440/60

The Range works in columns with just a simple value (i.e. not formula) so I'm thinking the error that I receive is caused from this. This is the error:
Run-time error '1004':
Application-defined or object-defined error

Thank you,
vkimura
0
Comment
Question by:Victor Kimura
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:ioane
ID: 24036092
Not reallysure what you're trying to accomplish there, but try this:

Range("N2:X65536").End(xlUp).Row
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24036171

Note that you rcode appears to be using "X1up" not "Xlup" - this will be causing an error

MsgBox Range("N2:N" & Cells(65536, "X").End(xlUp).Row).Address
will return a range address of N2:Nnumber where number is the last used cell in column X
Cheers
Dave


0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24036267
And FYI it's better to use Rows.count rather than hardcoding 65536 in - if you upgrade to 2007 or later your code may not work properly
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24036337
<Rory ..... baby not asleep or you are an early riser?>
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 24036643
Both! (They are somewhat related) She was up at 12, 2 and 5, when I have to get up anyway! :)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Victor Kimura
ID: 24056602
Hi brettdj,

Hi ran your code:
MsgBox Range("N2:N" & Cells(65536, "X").End(xlUp).Row).Address

I receive (N2:N29). Is there is way to retrieve the values of N2:N29. Currently, there is a formula in the field:
=(M2-L2)*1440/60

This formula essentially takes two times (1:30 - 12:30)*1440/60 and produces 1. Which means one hour of class session. When I try:
MsgBox Range("N2:N" & Cells(65536, "X").End(xlUp).Row)

I receive an error.

Any idea?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 24066322
Hi,
>Is there is way to retrieve the values of N2:N29
Yes. The code below displays all the addresses and relataed values in the Immediate window of the VBE
But what are you looking to do with the VBA, return all 28 values?
Cheers
Dave



Sub GetEm()

    Dim rng1 As Range, cel As Range

    Set rng1 = Range("N2:N" & Cells(Rows.Count, "X").End(xlUp).Row)

    For Each cel In rng1

        Debug.Print cel.Address & " " & cel.Value

    Next cel

End Sub

Open in new window

0
 

Author Comment

by:Victor Kimura
ID: 24074275
Hi brettdj,

I wanted to return the values in this range and then simply copy them to another sheet. But I think I can figure the copying of the code to another worksheet. BTW, I'm not certain what the Debug.Print does but I didn't see anything when I executed the code. I viewed the values by adding the variable to the Add Watch list and created breakpoints to view the values.

Thank you for your help.
vkimura
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 500 total points
ID: 24074365
something like this will copy that range to B2 on the second sheet
see attached file
Cheers
Dave

Sub GetEm()

    Dim rng1 As Range

    Set rng1 = Range("N2:N" & Cells(Rows.Count, "X").End(xlUp).Row)

    rng1.Copy Sheets(2).Range("b2")

End Sub

Open in new window

sample---DJB.xls
0
 

Author Closing Comment

by:Victor Kimura
ID: 31565162
Hi brettdj,

Thanks so much for your help. You were very helpful.

Cordially,
vkimura
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now