VBA How Do I Edit Cell Contents Based On Contents Of Two Other Cells In Same Row
I need help creating a macro for cleaning up a csv sheet that contains product sales information including the shipping method that needs to be used. There are about 10 of our products that cannot be sent via our usual shipping methods and I need to overwrite the shipping method that is assigned to orders that contain these products.
- Column A is order numbers
- Column B is the product purchased
- Column C is the shipping method
If someone purchases multiple products in one order the order number is duplicated in the next row, the shipping method only displays in the first row with that order number and each consecutive product purchased in the same order just adds a new row with the product name in column B
I need to use VBA to check for a list of 10 or so specific products in column B and update the shipping method for the whole order (the upper most cell in column C that has the same value in cell A) if it contains one of these 10 products.
I think it needs to do something like this:
- look at column B and search for a string of text
- if cell matches the search criteria look at the value in column A in the same row
- find the first instance of this value in Column A
- replace the contents in Column B in the same row with the new shipping method
I would need this in a loop so that it does it for each instance of the search string in column B in the spreadsheet.
I hope this question is clear, I have searched SO and can't find an answer to this question. Any help or alternative fixes are welcome.
Answer
Try out the following code, given the setup and result in the following screenshots:
Sub test()
Dim pRange As Range
Dim nShip As Range
Set pRange = Range("E2")
pRange.Select
While ActiveCell.Value <> ""
Range("B2").Select
While ActiveCell.Value <> ""
If ActiveCell.Offset(0, -1).Value <> ActiveCell.Offset(-1, -1).Value Then
Set nShip = ActiveCell.Offset(0, 1)
End If
If ActiveCell.Value = pRange.Value Then
nShip.Value = pRange.Offset(0, 1).Value
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Wend
Set pRange = pRange.Offset(1, 0)
pRange.Select
Wend
End Sub
Starting List:
Result after macro:
Related Questions
- → OctoberCMS : Backend page not found
- → Mobile vs Desktop Scroll/Resize Issues
- → 4th Modal Prevents NavBar Dropdown Menu From Functioning?
- → reactJS how to pass state when going to another URL? (clicking "New" on navbar that goes to Submit form)
- → Unable to access function from parent - sending props from parent to child
- → Laravel dynamic page title in navbar-brand
- → VBA how do I edit cell contents based on contents of two other cells in same row
- → Bootstrap Nav Collapse via Data Attributes Not Working
- → Set the color of a link that is loaded from the jQuery '.load()' method
- → Shopify sticky navbar on mobile size is not showing?
- → Navbar Links Not Working
- → VBA Excel run javascript form event
- → React-bootstrap elements does not have styling