Solved

excal vba problem: range

Posted on 2003-12-08
4
297 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
  • 3
4 Comments
 
LVL 80

Accepted Solution

by:
byundt earned 100 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 80

Expert Comment

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

Expert Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

744 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

9 Experts available now in Live!

Get 1:1 Help Now