Range selection

[Ram Chandran]

Hi Guys,

I have attached a Excel worksheet.
In that I need column New Name(Table 1) should be assigned based on the numeric values which i shown in Table 2.
It should assing automatically whenever i change value in the Table 2.
Kindly help me to sort out this work using VBA.
Thank in Advance.

Thanks & Regards,
Ramachandran.

 

[Marc L]

Hi, here a VBA procedure seems useless : why not using a direct formula ?!​

​

 

[Ram Chandran]

Hi Marc,

I need this in VBA because i want to change the values so according to that my new name should change.
and also the data i am handling is more 8 lakhs rows. So only.

Thanks for you feedback.

 

[Marc L]

As if you correct Table 2 From column - To column is useless - then the VLOOKUP formula works automatically faster than a VBA procedure​

and this formula can't fail but a VBA procedure could …​

​

And your thread title does not well reflect your need !​

​

 

[Marc L]

Anyway according to your attachment an useless VBA event procedure​

- as it just reproduces what the Excel formula achieves directly -​

when a change is made into From column F9:F13 - as a reminder greater than 15 can't be 15 ! -​

to paste only to the Sheet1 worksheet module :​

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect([F9:F13], Target) Is Nothing Then
        Application.EnableEvents = False
    With [_FilterDatabase].Rows("2:" & [_FilterDatabase].Rows.Count).Columns
        .Item(3) = Application.VLookup(Application.Round(.Item(2), 0), [F8:H13], 3)
    End With
        Application.EnableEvents = True
    End If
End Sub

Do you like it ? So thanks to click on bottom right Like !​

​

 

[Ram Chandran]

Hi Marc,

Thank you for your reply.
This is the part of my overall VBA sheet. So only i requested.

If possible can you please tell me the Vlookup formula for this.

Thanks in advance.

 

[Marc L]

Same as within the code like in cell C4 =VLOOKUP(ROUND(B4,0),$F$8:$H$13,3) …​

​

 

[Ram Chandran]

Hi Marc,

You have given this formula for considering lookup value from F column only.
What I need is
- Values from F8 to G8 should assign H8
- Values from F9 to G9 should assing H9
if I change the range (i.e., F or G) value it should automatically change in the New Name column accordingly.
Kindly help me for this.

Thanks in advance.

 

[Marc L]

As I wrote column G 'To' is useless according to Excel rules so VLOOKUP needs only column F​

like you should have seen just trying it before to write your post or just reading Excel help …​

And again with the same reminder : >15 can't be 15 neither >200 can't be 'above' as well !​

Formula works as expected once column F is correct.​

​

 

[p45cal]

In the linked-to file below, a formula-only solution.
Note:
1. Formula in D4, copied down. =XLOOKUP(B4,$G$8:$G$13,$H$8:$H$13,,1)
2. Only columns G & H of the table are used in the formula
3. The table is sorted descending
4. There is a check column E, all whose values are TRUE which can be deleted

Link: https://app.box.com/s/bvssw3giwik70zl6ezot6fn4q6f2xom2

 

[Ram Chandran]

Hi p45cal,

Thank you for your response.
I need that between two value ranges some name should be assign.
like if we put number filter in the excel.
please refer the screenshot attached.

Is it possible we have any vba to filter range.?
Kindly help.

Thanks in advance.

 

[p45cal]

Is it possible we have any vba to filter range.?

Why?!

I need this in VBA because i want to change the values so according to that my new name should change.
and also the data i am handling is more 8 lakhs rows.

All the above already happens automatically with any value change.