Solved

excal vba problem: range

Posted on 2003-12-08
4
323 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 81

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 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Some time ago I was asked to create a VBA function that would calculate a check digit for an input number, using the following procedure: First, sum up all the individual digits in the number If that sum value has more than one digit, then sum up …
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

790 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