[RESOLVED]Macro Modification

dear friend below macro work fine for me.only the thing is this macro final answer giving as a value.
ex- =250-50=200
this macro giving that final answer i mean 200.but i need answer like this =250-50
when i click that cell it should =250-50 like this.not only =200
pls any body can modify this macro.other whole thing in macro work very well.

Code:

---

Sub PackingToProductionUpdate()
Dim LotFind As Range, Lots As Range, LotNum As Range
Dim LotVal As Double
Application.ScreenUpdating = False

Set Lots = Sheets("Packing").Range("F5", Range("F" & Rows.Count).End(xlUp))
On Error Resume Next

For Each LotNum In Lots
    With Sheets("Production")
        Set LotFind = .Columns("K:K").Find(Left(LotNum, 6), After:=.Range("K" & Rows.Count), LookIn:=xlValues, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

        If Not LotFind Is Nothing Then
            LotVal = LotFind.Offset(0, -1).Value - LotNum.Offset(0, 1).Value
                If LotVal <= 0 Then
                    LotFind.EntireRow.Delete xlShiftUp
                Else
                    LotFind.Offset(0, -1).Value = LotVal
                End If
            LotNum.EntireRow.ClearContents
            Set LotFind = Nothing
        End If
    End With
Next LotNum

Application.ScreenUpdating = True
End Sub

---

i understand what you want but am unsure where it applies, the only place i can see is

Quote:

---

LotFind.Offset(0, -1).Value = LotVal

---

try

vb Code:

LotFind.Offset(0, -1).Value = "'=" & LotFind.Offset(0, -1).Value & " - " & LotNum.Offset(0, 1).Value

alternatively you could format the cells as text

Quote:

---

Originally Posted by westconn1

i understand what you want but am unsure where it applies, the only place i can see is


try

vb Code:

LotFind.Offset(0, -1).Value = "'=" & LotFind.Offset(0, -1).Value & " - " & LotNum.Offset(0, 1).Value

alternatively you could format the cells as text

---

thanks a lot friend.it's work very well.REP+ add.thanks a lot again.:thumb::thumb:

ohh. i have facing another problem here.if one time i'm reducing value above code work well.if i have reduce two time that answer is correct but i need like this,
ex-
one time we reduce values it's ok =200-50
if i reduce value again answer coming =150-50
it should =200-50-50
pls help me...

LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value

Quote:

---

Originally Posted by anhn

LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value

---

dear anhn your code & westconn1's code both are same.my problem is not solved.:confused:

i don't understand where it can reduce or how many times

also anhn's code is substantially different, to what i posted previously

Quote:

---

dear anhn your code & westconn1's code both are same.my problem is not solved.

---

They are not the same.

pete's code gives value as a string
"=200-50"

My code produces a formula
=200-50
this formula gives a value of 150 in next round for the next formula:
=150-50

dear westconn1 & anhn pls don't get angry with me.my English is too bad.I'll try explain my requirement.anhn your understanding is correct.if i run this macro twice answer should =200-50-50.not =150-50.pls help me.

try changing to
LotFind.Offset(0, -1).Value = "'=" & LotFind.Offset(0, -1).Text & " - " & LotNum.Offset(0, 1).Text

without seeing the contents of the cells it is a bit hard to guess

Quote:

---

Originally Posted by westconn1

try changing to
LotFind.Offset(0, -1).Value = "'=" & LotFind.Offset(0, -1).Text & " - " & LotNum.Offset(0, 1).Text

without seeing the contents of the cells it is a bit hard to guess

---

sorry sir.same answer.:confused::confused:

what is the content of lotfind.offset(0, -1) when the code runs?
if it already contains the 150 value i do not see how you can translate that back, where is the cell containing =200 - 50 in relation to lotfind?

maybe post a sample workbook, with some data and the code
zip to attach to post
if you are using 2007 saveAs earlier version, as not all have 2007

Quote:

---

Originally Posted by westconn1

what is the content of lotfind.offset(0, -1) when the code runs?
if it already contains the 150 value i do not see how you can translate that back, where is the cell containing =200 - 50 in relation to lotfind?

maybe post a sample workbook, with some data and the code
zip to attach to post
if you are using 2007 saveAs earlier version, as not all have 2007

---

here i have attached sample work sheet.pls check it..

Quote:

---

dear westconn1 & anhn pls don't get angry with me.my English is too bad.I'll try explain my requirement.anhn your understanding is correct.if i run this macro twice answer should =200-50-50.not =150-50.pls help me.

---

You said "me.my English is too bad.I'll" but your writing style is worst.
In most Latin languages, after a full stop (.) or a comma (,) you should type a space to make your writing easier to read. The above should be:

Quote:

---

dear westconn1 & anhn pls don't get angry with me. My English is too bad. I'll try explain my requirement. anhn your understanding is correct. If i run this macro twice answer should =200-50-50. Not =150-50. Pls help me.

---

Try this:

Code:

---

    If LotVal <= 0 Then
        LotFind.EntireRow.Delete xlShiftUp
    ElseIf LotFind.Offset(0, -1).HasFormula Then
        LotFind.Offset(0, -1).Formula = LotFind.Offset(0, -1).Formula & "-" & LotNum.Offset(0, 1).Value
    Else
        LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value
    End If

---

Quote:

---

Originally Posted by anhn

You said "me.my English is too bad.I'll" but your writing style is worst.
In most Latin languages, after a full stop (.) or a comma (,) you should type a space to make your writing easier to read. The above should be:


Try this:

Code:

---

    If LotVal <= 0 Then
        LotFind.EntireRow.Delete xlShiftUp
    ElseIf LotFind.Offset(0, -1).HasFormula Then
        LotFind.Offset(0, -1).Formula = LotFind.Offset(0, -1).Formula & "-" & LotNum.Offset(0, 1).Value
    Else
        LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value
    End If

---

---

i really appreciate your advice sir. this macro code also work great. thanks a lot again. REP+.

Your code contains some errors. This is better:

Code:

---

Sub packingupdate()
    Dim LotFind As Range, Lots As Range, LotNum As Range
    Dim LotVal As Double
   
    Application.ScreenUpdating = False
    With Sheets("Packing")
        Set Lots = .Range("F5", .Range("F" & .Rows.Count).End(xlUp))
    End With
    With Sheets("Production")
        For Each LotNum In Lots
            If Trim(LotNum.Value) <> "" Then
                Set LotFind = .Columns("K:K").Find(Left(LotNum, 6), After:=.Range("K" & Rows.Count), _
                              LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
                              SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
                If Not LotFind Is Nothing Then
                    LotVal = LotFind.Offset(0, -1).Value - LotNum.Offset(0, 1).Value
                    If LotVal <= 0 Then
                        LotFind.EntireRow.Delete xlShiftUp
                    ElseIf LotFind.Offset(0, -1).HasFormula Then
                        LotFind.Offset(0, -1).Formula = LotFind.Offset(0, -1).Formula & "-" & LotNum.Offset(0, 1).Value
                    Else
                        LotFind.Offset(0, -1).Formula = "=" & LotFind.Offset(0, -1).Value & "-" & LotNum.Offset(0, 1).Value
                    End If
                    LotNum.EntireRow.ClearContents
                End If
            End If
        Next
    End With
    Application.ScreenUpdating = True
End Sub

---