VBA Help: input new value based on criteria

[gmmark1125]

Hello,

I have a workbook that we are using at work to track material allocations to work orders. What it does is show the customer order, the material it is short on, the job that exists that could satisfy the shortage, the quantity remaining of that job to fulfill the shortage, and the next available job to fulfill the quantity. What I need is for the record that has a value less than zero in the "Shortage Fulfillment Job Remaining Quantity" to update the value in the "Shortage Fulfillment Job" column with the job displayed in the "Next Available Fulfillment" column. The "Next Available Fulfillment" column

I have tried doing this with just formulas but it creates a circular reference problem. I figured VBA was the way to go with this but I am no where near skilled enough with VBA to do so. Any help is greatly appreciated!

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Order#[/TD]
[TD]Material Short[/TD]
[TD]Shortage Fulfillment Job[/TD]
[TD]Shortage Fulfillment Job Remaining Qty[/TD]
[TD]Next Available Fulfillment[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Mtl1[/TD]
[TD]Job A[/TD]
[TD]2[/TD]
[TD]Job B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Mtl1[/TD]
[TD]Job A[/TD]
[TD]1[/TD]
[TD]Job B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Mtl1[/TD]
[TD]Job A[/TD]
[TD]0[/TD]
[TD]Job B[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Mtl1[/TD]
[TD]Job A[/TD]
[TD]-1[/TD]
[TD]Job B[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mumps]

Try:

Sub ReplaceVal()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("D2:D" & LastRow)
        If rng < 0 Then
            rng.Offset(0, -1) = rng.Offset(0, 1)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

 

[Truiz]

Try this and change the colum references as needed

Sub fgd()

Dim lrow as Long

lrow = ActiveSheet.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row
    
For iCntr = lrow To 1 Step -1
    If Cells(iCntr, "D") < 0 Then
    Cells(iCntr, "C") = Cells(iCntr, "E")
    End If
Next
End Sub

 

[gmmark1125]

This one worked! Thank you!