IF Column E= "3100" and Column D="GLD" then put "3150" in Column E

CLCoop

Board Regular
Joined
May 30, 2018
Messages
56
Basically I have a code that changes (column d, BOC) into general ledger numbers converting all 3100-3140 into 3100-3157 however ONLY one Branch (column c, named branches) named "GLD" doesn't need to have its BOC changed it needs to stay 3100-3157 or convert back to 3100-3157. However it isn't always on the same row and there maybe several rows with this data.

This is the code I'm using to convert over 1000 rows with 15 different branches into 3100-3140:

Columns("D:D").Select
Selection.Replace What:="3100-3157", Replacement:="3100/3140 - Equipment w/ Maintenance", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False


Is there a way to either tell my code not to convert GLD (Column c, named branches) if it has 3100/3157 in (Column C, boc), or is it easier to then convert the new information of 3100-3140 back to 3100-3157 (Column C)if the Branch = GLD (Column D)?

Thank you for your help. I know I can do several if statements formulas but not sure what to do when it comes to VBA
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
Does this do what you want...

Code:
Sub test()


    Dim lRow As Long, i As Long
    
    Application.ScreenUpdating = False
    lRow = Cells(Rows.Count, 4).End(xlUp).Row
    For i = 2 To lRow
        If Not Cells(i, 3) = "GLD" And Cells(i, 4) = "3100-3157" Then
            Cells(i, 4) = "3100/3140 - Equipment w/ Maintenance"
        End If
    Next
    Application.ScreenUpdating = True
    
End Sub
 
Upvote 0
Awesomesauce!!! I was able to use the code and adapt to several other scenarios. Thank you for saving me a lot of time overthinking this. :cool:
 
Upvote 0
I was happy to help. Thanks for the feedback!
 
Upvote 0

Forum statistics

Threads
1,213,482
Messages
6,113,908
Members
448,532
Latest member
9Kimo3

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