Find and replace cells with criteria

[Kenneth Jay Urbano]

Hi Experts, I need your help I'm a newbie here. What I want to do is to find and replace a cell value with the given criteria. Please see attached image for reference.
From my Raw data I searched by customer name with a string like "new" so it displayed all customer name that have a "new" in it. This is working.

Now my problem is, how can I replace all "New York" Quantity into 500. Anyone? the out put should be the displayed output on my attachment. Please

 

[Hui]

Kenneth

Firstly, Welcome to the Chandoo.org Forums

Can you please post the file so we can give a specific response

 

[Kenneth Jay Urbano]

Kenneth

Firstly, Welcome to the Chandoo.org Forums

Can you please post the file so we can give a specific response

Hi sir thank you for the reply, here is the file. Is it possible to use a query like mysql. For example if i want to replace the quantity into 500 for all customer name "New York". is it possible to use something like this "Update table1 set Quantity= '500' where customer_name = 'New York'. Thank you sir.

 

[Nebu]

Hi:

Is it compulsory to use SQL??

Thanks

 

[Kenneth Jay Urbano]

Hi:

Is it compulsory to use SQL??

Thanks

Hi sir nebu, I want to use none other than excel as my main database where i can filter and replace cells with a given criteria.

 

[Nebu]

Sub test()
Dim t1 As ListObject
Dim array1 As Variant
Dim i As Long
Set t1 = Sheet1.ListObjects(1)
array1 = t1.DataBodyRange
For i = LBound(array1) To UBound(array1)
    If t1.DataBodyRange(i, 2) Like "New York" Then
    t1.DataBodyRange(i, 4) = 5000
    End If
Next i
   
End Sub

This code will basically loop through your table and change the quantity for the name New York.

Hope this is what you were looking for.

Thanks

 

[Kenneth Jay Urbano]

Sub test()
Dim t1 As ListObject
Dim array1 As Variant
Dim i As Long
Set t1 = Sheet1.ListObjects(1)
array1 = t1.DataBodyRange
For i = LBound(array1) To UBound(array1)
    If t1.DataBodyRange(i, 2) Like "New York" Then
    t1.DataBodyRange(i, 4) = 5000
    End If
Next i
 
End Sub

This code will basically loop through your table and change the quantity for the name New York.

Hope this is what you were looking for.

Thanks

Hi sir thanks for the reply, thats exactly the logic but what if instead of using the word "New York" it should be using Textbox to search for the world then replace quantity.

I Tried:
If t1.DataBodyRange(i, 2) Like "* & Textbox1.text & *" and
If t1.DataBodyRange(i, 2) = "*" & textbox.text & "*" but its not working.

 

[Smallman]

Hi Kenneth

Place New York in cell B1 and 5000 in cell D1 so the criteria is not hard coded into the VB back end. Use this two cell to drive your file changes.

No loop required.

Sub Chango()
    Sheet1.ListObjects("Table1").Range.AutoFilter 2, [B1]
    Range("Table1[Quantity]") = [D1]
End Sub

@Nebu

It is fantastic that you have posted your coding. Can you continue to do this as most visitors never join and will not see anything inside a file. Everyone wins this way.

Take care

Smallman

 

[Nebu]

Hi:

I guess you got the reply from Smallman, but if you want to use user form you can give text box reference instead of cell reference.
Thanks

 

[Smallman]

A useful way to handle direct input would be to add a data validation list. This way you are taking free text out of the equation. I would steer clear of free text and have the cities in a list tab, originally titled "Lists" and get your users to choose from the list. That choice triggers a change event that runs your macro.

Take care

Smallman

 

[Kenneth Jay Urbano]

Hi Sir nebu and smallman, thanks for replying. Ill try this late, let you know if its working. Thank you again much appreciated