Negative number search for each row then paste to another location

[Shawnz]

Hi all,

Need help on macro script which able to detect negative value in cell.
After detection, the macro need to copy the value -> paste in Abs value after deducting total processing weeks required in another cell.
In excel as shown below:
Need the marco to detect neg value -11 on 14 Sep ( AH34 cell) [Stock level]
Paste the neg value as positive into 3 Aug field [Ordering mode] after subtracting the total processing of 5 weeks (V34 cell)
[TABLE="width: 922"]
<colgroup><col><col><col><col span="4"><col span="5"></colgroup><tbody>[TR]
[TD="colspan: 2"]Stock level[/TD]
[TD]Weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD]Part No[/TD]
[TD]Total Processing[/TD]
[TD]03-Aug[/TD]
[TD]10-Aug[/TD]
[TD]17-Aug[/TD]
[TD]24-Aug[/TD]
[TD]31-Aug[/TD]
[TD]07-Sep[/TD]
[TD]14-Sep[/TD]
[TD]21-Sep[/TD]
[TD]28-Sep[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9000[/TD]
[TD]LABEL003[/TD]
[TD]5[/TD]
[TD="align: left"]

<tbody>
</tbody>

[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD]-11[/TD]
[TD]-33[/TD]
[TD]-53[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="colspan: 2"]Ordering Mode[/TD]
[TD]Weeks[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]No.[/TD]
[TD]Part No[/TD]
[TD]Total Processing[/TD]
[TD]03-Aug[/TD]
[TD]10-Aug[/TD]
[TD]17-Aug[/TD]
[TD]24-Aug[/TD]
[TD]31-Aug[/TD]
[TD]07-Sep[/TD]
[TD]14-Sep[/TD]
[TD]21-Sep[/TD]
[TD]28-Sep[/TD]
[/TR]
[TR]
[TD]9000[/TD]
[TD]LABEL003[/TD]
[TD]5[/TD]
[TD="align: right"]11[/TD]
[TD="align: right"]33[/TD]
[TD="align: right"]53[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]73[/TD]
[TD="align: right"]85[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]115[/TD]
[TD="align: right"]135[/TD]
[/TR]
</tbody>[/TABLE]


Hope my explanation is clear.. Appreciate all help to resolve program code.
Thank you.

 

[Shawnz]

Bumping it up

 

[Shawnz]

Need help here : )

 

[Shawnz]

bumping up

 

[sanrv1f]

Not sure why you need macro for this, if you are fine with a formula solution, try this

assuming first dataset is in Stock level sheet A1:B30
and the second dataset is in Ordering mode sheet A2:L4

D3

=IFERROR(ABS(VLOOKUP($B3,'Stock level'!$B$2:$L$30,MATCH(D$2+($C3+1)*7,'Stock level'!$B$2:$L$2,0),0)),"")

 

[Shawnz]

Hi,

I am trying to create a ordering report where user will be able to see item required to be ordered after considering the lead time required and shortage ahead.

Thus if stock level shortages are found eg: today + 5 weeks later, the ordering report will request user to place the order out today.

Hope this will clear up my intention better.
Thank you.

 

[Shawnz]

Hi,

I am trying to create a ordering report where user will be able to see item required to be ordered after considering the lead time required and shortage ahead.

Thus if stock level shortages are found eg: today + 5 weeks later, the ordering report will request user to place the order out today.

Hope this will clear up my intention better.
Thank you.

Bumping it up

 

[Shawnz]

Bumping it up