CODEWITHSUNDEEP
excelvba
dd043
dd043

Reputation: 11

Increasing Value in Column by X if Value in Another Column is Like Y

I am looking for some help adjusting my VBA. I believe I am close but can't figure out the final necessary steps. I want to increase the value in column K by X percent if the string in column C is like X. Below is what I have already, please let me know if there is any further information needed. Thanks!

Sub Cost_Increase()

Dim strInput As String
strInput = InputBox("Input Part Number", "Part Number for Cost Increase", "Enter your input text HERE")


Dim iInput As Integer
iInput = InputBox("Please enter an increase amount", "Increase Customer Cost")

Dim r As Long, ws As Worksheet

Set ws = Sheets("All Cust")

For r = ws.Cells(Rows.Count, "C").End(xlUp).Row To 2 Step -1
    
If ws.Range("C" & r).Value Like strInput Then
        
ws.Range("K" & r).Value = ((ws.Range("K" & r).Value * iInput) + ws.Range("K" & r).Value)
    
End If

Next r

End Sub

Upvotes: 1

Views: 41

Answers (2)

VBasic2008
VBasic2008

Reputation: 54838

Increase By Percentage

A Quick Fix

Option Explicit

Sub Cost_Increase()

    Dim PartNumber As String
    PartNumber = InputBox("Input Part Number", "Part Number for Cost Increase", "Enter your input text HERE")
    If Len(PartNumber) = 0 Then Exit Sub ' canceled or no entry: improve!
    
    Dim Increase As String
    Increase = InputBox("Please enter an increase percentage", "Increase Customer Cost", "0")
    If Len(Increase) = 0 Then Exit Sub ' canceled or no entry: improve!
    If Not IsNumeric(Increase) Then Exit Sub ' not a number: improve!
    
    Dim ws As Worksheet: Set ws = ThisWorkbook.Worksheets("All Cust")
    
    Dim r As Long
    For r = ws.Cells(ws.Rows.Count, "C").End(xlUp).Row To 2 Step -1
        If InStr(1, CStr(ws.Range("C" & r).Value), PartNumber) = 1 Then ' begins with
            If IsNumeric(ws.Range("K" & r).Value) Then
                ws.Range("K" & r).Value _
                    = ws.Range("K" & r).Value * (100 + Increase) / 100
            End If
        End If
    Next r

End Sub

Upvotes: 1

CallumDA
CallumDA

Reputation: 12113

From your question, it's a little ambiguous what you actually have in column C, and that makes a difference to the answer. I've assumed you might have a string like "Item01" and you want to identify it using "Item" or "01", or any other subset of that string.

As mentioned in the comments, based on your code it doesn't seem necessary to loop backwards, so I've also removed that.

Sub Cost_Increase()

    Dim findString As String, multiplier As Long, colC As Range, r As Range
    
    findString = InputBox("Input Part Number", "Part Number for Cost Increase", "Enter your input text HERE")
    multiplier = InputBox("Please enter an increase amount", "Increase Customer Cost")
    
    With ThisWorkbook.Worksheets("All Cust")
        Set colC = .Range(.Range("C2"), .Range("C1048576").End(xlUp))
    End With
    
    For Each r In colC
        If InStr(r.Value, findString) > 0 Then
            r.Offset(0, 8).Value = r.Offset(0, 8) * (1 + multiplier)
        End If
    Next r

End Sub

Note, also, how the For Each loop is assigning a reference to each looped cell to r. This is preferred to creating the cell references manually by looping through the numbers 2 to N.

Upvotes: 0

Related Questions