[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 403
  • Last Modified:

Range Value from formula

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
Victor Kimura
Asked:
Victor Kimura
  • 4
  • 3
  • 2
  • +1
1 Solution
 
ioanePlanning & Analytics ManagerCommented:
Not reallysure what you're trying to accomplish there, but try this:

Range("N2:X65536").End(xlUp).Row
0
 
Dave BrettVice President - Business EvaluationCommented:

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
 
Rory ArchibaldCommented:
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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
Dave BrettVice President - Business EvaluationCommented:
<Rory ..... baby not asleep or you are an early riser?>
0
 
Rory ArchibaldCommented:
Both! (They are somewhat related) She was up at 12, 2 and 5, when I have to get up anyway! :)
0
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
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
 
Dave BrettVice President - Business EvaluationCommented:
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
 
Victor KimuraSEO, Web DeveloperAuthor Commented:
Hi brettdj,

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

Cordially,
vkimura
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now