Range Variable (From Cell Value)

[RudRud]

Hi Gurus,

If vbNo , inpboxa & b will become as a range variable, therefore when i put inpboxa.select it will be selected

However if vbYes, inpboxa cant be select. Any quick thoughts for selecting an inpboxa when vbyes? Thanks

Sub test()

answer = MsgBox("Last Range (Yes), New Range(No) ", vbYesNo)
If answer = vbYes Then
inpboxa = Range("b2").Value
inpboxb = Range("b3").Value

ElseIf answer = vbNo Then
    Set inpboxa = Application.InputBox(Prompt:="Select a range", Type:=8)
   Set inpboxb = Application.InputBox(Prompt:="Select a range", Type:=8)
   Range("b2").Value = inpboxa.Address
   Range("b3").Value = inpboxb.Address
End If

inpboxa.Select
End Sub

 

[Joe4]

In your code, inpboxa and inpboxb are NOT range variables! You have set them equal to the values in the cells, not to the cells themselves.
This is another great example why you should always declare all your variables before using them. It will help prevent you from making these sort of mistakes.
You also need to use "Set" when setting object variables, like range variables.

Try this:

Dim inpboxa as Range
Dim inpboxb as Range
Set inpboxa = Range("b2")
Set inpboxb = Range("b3")

 

[RudRud]

In your code, inpboxa and inpboxb are NOT range variables! You have set them equal to the values in the cells, not to the cells themselves.
This is another great example why you should always declare all your variables before using them. It will help prevent you from making these sort of mistakes.
You also need to use "Set" when setting object variables, like range variables.

Try this:

Dim inpboxa as Range
Dim inpboxb as Range
Set inpboxa = Range("b2")
Set inpboxb = Range("b3")

Hi @Joe4, Well Noted, Thanks for your kind information.

But when vbyes, inpboxa.select not working.

 

[Joe4]

Your code works just fine for me.
Please walk us through an example of how it is not working.
What are you entering in for each prompt, and what does it do?

 

[RudRud]

Your code works just fine for me.
Please walk us through an example of how it is not working.
What are you entering in for each prompt, and what does it do?

Apologies,

What i mean is If Vbyes, Then inpboxa become a range from b2 value (which is a2 a10)

When VBYes inpboxa.select become A2:A10 select.

 

[DanteAmor]

In VByes you must set the range contained in the cell.
Try this:

Sub test()
  Dim inpboxa As Range, inpboxb As Range
  Dim answer As VbMsgBoxResult
 
  answer = MsgBox("Last Range (Yes), New Range(No) ", vbYesNo)
  If answer = vbYes Then
    Set inpboxa = Range(Range("b2").Value)
    Set inpboxb = Range(Range("b3").Value)
 
  ElseIf answer = vbNo Then
    On Error Resume Next  'Use in case of pressing cancel.
    Set inpboxa = Application.InputBox(Prompt:="Select a range", Type:=8)
    Set inpboxb = Application.InputBox(Prompt:="Select a range", Type:=8)
    If inpboxa Is Nothing Then Exit Sub
    If inpboxb Is Nothing Then Exit Sub
    On Error GoTo 0
    Range("b2").Value = inpboxa.Address
    Range("b3").Value = inpboxb.Address
  End If

  inpboxa.Select
  inpboxb.Select
End Sub

 

[Joe4]

  inpboxa.Select
  inpboxb.Select

Not really sure what the point of that would be.
The second select would simply overwrite the first, would it not?

 

[RudRud]

Thanks all Gurus for the assists, truly appreciated.

I found out Dante's code and this's the key part that i've been debugging a while (solution)

Set inpboxa = Range(Range("b2").Value)

--

Actually Im working with checking different values from ranges

If vbNo then user will require to select a new range, if vbyes then based in lastrange.

Book2
	A
2	asd
3	asd
Sheet2

Book2
	B
2	Rud
3	Asd
Sheet3

then the ranges will become two arrays , through array it will compare one by one based on two selected ranges.

 

[Joe4]

Thanks all Gurus for the assists, truly appreciated.

I found out Dante's code and this's the key part that i've been debugging a while (solution)

Set inpboxa = Range(Range("b2").Value)

--

Actually Im working with checking different values from ranges

If vbNo then user will require to select a new range, if vbyes then based in lastrange.

Book2
	A
2	asd
3	asd
Sheet2

Book2
	B
2	Rud
3	Asd
Sheet3

then the ranges will become two arrays , through array it will compare one by one based on two selected ranges.

I am glad he was able to guess what it was that you wanted to do. It wasn't quite clear from your original post. You posted the code, but did not explain what you were ultimately trying to accomplish with it.

 

[RudRud]

I am glad he was able to guess what it was that you wanted to do. It wasn't quite clear from your original post. You posted the code, but did not explain what you were ultimately trying to accomplish with it.

I will take note for that and i'll try to be more clearer in the future. Thanks for the fast response anyway