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.
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
Result after macro:
- → 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
- → React-bootstrap elements does not have styling