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
Last edited by johncena; Mar 22nd, 2010 at 06:44 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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...
i don't understand where it can reduce or how many times
also anhn's code is substantially different, to what i posted previously
Last edited by westconn1; Mar 22nd, 2010 at 05:59 AM.
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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.
without seeing the contents of the cells it is a bit hard to guess
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case. Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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..
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:
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
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
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+.
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
Don't forget to use [CODE]your code here[/CODE] when posting code
If your question was answered please use Thread Tools to mark your thread [RESOLVED]
[RESOLVED]Macro Modification
Reply With Quote
Re: Macro Modification
Originally Posted by westconn1
