Go Premium for a chance to win a PS4. Enter to Win

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

excal vba problem: range

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
marrydale
Asked:
marrydale
  • 3
1 Solution
 
byundtCommented:
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
 
marrydaleAuthor Commented:
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
 
byundtCommented:
marry,
Set rangeA = Nothing
Brad
0
 
byundtCommented:
marry,
Thanks for the grade!
Brad
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

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