Selecting variables based on values

A

alion

New Member
Joined
Aug 4, 2011
Messages
19
So I have some variables and their values like this:

AV1 2.3
AV2 1.2
AV3 3.0
AV4 3.1
AV5 2.5

Now if I want VBA to show me stocks with values between 2 and 3, it should give me AV1, AV3 and AV5 considering 3.0 was inclusive. How to do this? any clues?

thanks in advance
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Sort by date Sort by votes
alion said:
So I have some variables and their values like this:

AV1 2.3
AV2 1.2
AV3 3.0
AV4 3.1
AV5 2.5

Now if I want VBA to show me stocks with values between 2 and 3, it should give me AV1, AV3 and AV5 considering 3.0 was inclusive. How to do this? any clues?

thanks in advance
Click to expand...

Not sure if this helps, but if the list of variables are in Columns A and B maybe this?

Code: 
Sub Alion()
Dim lr As Long
Dim lr2 As Long

lr = Cells(Rows.Count, 1).End(3).Row



With Range("C1:C" & lr)

    .Formula = "=IF(AND(B1>2,B1<=3),A1,"""")"
    .Value = .Value
    .AutoFilter
    .AutoFilter Field:=1, Criteria1:="<>"
    .SpecialCells(xlCellTypeVisible).Copy Range("X1")
    .AutoFilter
    
End With

lr2 = Cells(Rows.Count, 24).End(3).Row

MsgBox Join(WorksheetFunction.Transpose(Range("X1:X" & lr2)), vbLf)

End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Alternate to nested IFs, comparing values in between percentages
Replies
3
Views
183
Fluff
Fluff
G
  • Solved
Variable not storing value
2
Replies
14
Views
567
Gadgetgav
G
L
  • Question Question
Selection based on two variables - copy and paste to another sheet
Replies
3
Views
391
kvsrinivasamurthy
K
R
  • Question Question
How can I get the value of a specific column in a range.
Replies
4
Views
540
Scott Huish
Scott Huish
M
  • Solved
Deleting rows from a Range variable
Replies
4
Views
298
Umma123
U

Forum statistics

Threads
1,224,525
Messages
6,179,319
Members
452,905
Latest member
deadwings

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top