Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

excal vba problem: range

Posted on 2003-12-08
4
Medium Priority
?
344 Views
Last Modified: 2008-02-01
Hi,
I have some coding as below, but i just don't know how to continue it:

......
dim rangeA as Range
dim sht as Worksheet
set sht=ActiveSheet
......
if aaa+1<>bbb then
       set rangeA = Range(sht.Cells(aaa+1,1), Cells(bbb-1,sht.UsedRange.Columns.Count))
end if

If <expression here!> Then
      msgbox "rangeA is valid"
      rangeA.select
      <more steps here....>
Else
     msgbox "rangeA is not valid"
End If

how i gonna write the code in the <expression here!> to determine whether the "rangeA" is a valid range or not. Meaning if aaa=2 and bbb=3, from the coding, we know that rangeA will be nothing. In the second "if-statement", i will check whether the rangeA is valid or not, if valid, i will do some manipulation on it, if not, i will continue to the next step. how to write it in code? hope somebody can help me on this a.s.s.p. I need it urgently. thanks!


cheers,
marry
0
Comment
Question by:marrydale
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 400 total points
ID: 9901018
Hi marrydale,
Try something like this:

Sub RangeTester()
Dim rangeA As Range
Dim sht As Worksheet
Set sht = ActiveSheet
Dim aaa As Long, bbb As Long
aaa = InputBox("aaa")
bbb = InputBox("bbb")
If aaa + 1 <> bbb Then
       Set rangeA = Range(sht.Cells(aaa + 1, 1), Cells(bbb - 1, sht.UsedRange.Columns.Count))
End If

If IsError(rangeA) Or rangeA Is Nothing Then
     MsgBox "rangeA is not valid"
Else
    MsgBox "rangeA is valid"
    rangeA.Select
    '<more steps here....>
End If
End Sub


Cheers!

Brad
0
 

Author Comment

by:marrydale
ID: 9901297
brad,
thanks, it help me a lot!

one small portion of question i forgot to ask just now, the rangeA actually is in a for-loop. so i have to reset it into nothing after it have run one loop. how to code it? meaning how to reset it into nothing?


cheers,
marry
0
 
LVL 81

Expert Comment

by:byundt
ID: 9901372
marry,
Set rangeA = Nothing
Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 9903820
marry,
Thanks for the grade!
Brad
0

Featured Post

Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This video walks the viewer through the process of creating Hyperlinks for the web and other documents. Select the "Insert" tab: Click "Hyperlink":  Type "http://" followed by a web address to reference a website or navigate to a document to ref…
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

670 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