coxycoxycoxy
Board Regular
- Joined
- Apr 22, 2009
- Messages
- 165
Can the Following process be achieved via VBA<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
ffice
ffice" /><o
></o
>




<o






We need to create a process for writing down the stock at the end of each year the following changes would need to be made on each of the 400 stock cards.<o


<o
> </o
>




1.) Copy & insert column I <o


2.) Change cell “I8” to “Cost Price Old”<o


3.) Change cell “J8” to “Cost Price New”<o


4.) Insert formula “=(I-J)/I” into column “O” from last row with data to “O10” & Change format to %<o


5.) Change “O8” to “% Diff” <o


6.) Change “R5” to reference last entry in column L<o


7.) Change “R6” to reference last entry in column N<o


This is what I have recorded via the Macro Recorder so far for points 1 to 7 <o


Code:
Columns("I:I").Select
Selection.Copy
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Range("I8:I9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Old Cost Price"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Bold"
.size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("J8:J9").Select
ActiveCell.FormulaR1C1 = "New Cost Price"
With ActiveCell.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Bold"
.size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("O40").Select
ActiveCell.FormulaR1C1 = "=(RC[-6]-RC[-5])/RC[-6]"
Range("O40").Select
Selection.AutoFill Destination:=Range("O10:O40"), Type:=xlFillDefault
Range("O10:O40").Select
Columns("O:O").Select
Selection.NumberFormat = "0%"
Range("I8:I9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Old Cost Price"
Range("R5:S5").Select
ActiveCell.FormulaR1C1 = "=R40C12"
Range("R6:S6").Select
ActiveCell.FormulaR1C1 = "=R40C14"
Range("R7").Select
End Sub
I would need help to change points 4, 6, 7 so that they referenced the last row with data.<o


This is where I would need to know if the following is possible. <o


<o


1.) We would need to find the last row of each year in column D10:D100 copy that row and insert it on the next row on the worksheet, it would then need to change the vales in column J above the inserted line to zero But only for the selected year.<o


<o


2.) One problem I can see is that if there is no stock movement of a item for 3 years<o


I would need it to insert a row for every year that has elapsed where there has been no movement.<o


<o


i.e if Stock came in, in 2007 but there was no stock movement until 2010 it would need to insert the last row of data for that year for years 2007, 2008, 2009,2010
3. For each year end figure 10% would need to be deducted
<o


<o


<o



<TABLE style="WIDTH: 866pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=1149><COLGROUP><COL style="WIDTH: 8pt; mso-width-source: userset; mso-width-alt: 365" width=10><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 34pt; mso-width-source: userset; mso-width-alt: 1645" width=45><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 84pt; mso-width-source: userset; mso-width-alt: 4096" width=112><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" span=2 width=62><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 50pt; mso-width-source: userset; mso-width-alt: 2450" span=2 width=67><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 19pt; mso-width-source: userset; mso-width-alt: 914" width=25><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 45pt; mso-width-source: userset; mso-width-alt: 2194" width=60><COL style="WIDTH: 28pt; mso-width-source: userset; mso-width-alt: 1353" width=37><COL style="WIDTH: 44pt; mso-width-source: userset; mso-width-alt: 2121" width=58><COL style="WIDTH: 20pt; mso-width-source: userset; mso-width-alt: 950" width=26><TBODY><TR style="HEIGHT: 8.25pt; mso-height-source: userset" height=11><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 8pt; HEIGHT: 8.25pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 height=11 width=10> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 width=74> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 34pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 width=45> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 57pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl414 width=75 colSpan=2> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 width=71> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 84pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 width=112> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 width=56> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl304 width=62> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 47pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl304 width=62> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 41pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl303 width=54> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 42pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl300 width=56> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl302 width=67> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 50pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl302 width=67> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl424 width=75> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 19pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl300 width=25> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 43pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl300 width=57> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 45pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl301 width=60> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 28pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl301 width=37> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 44pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl300 width=58> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; WIDTH: 20pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl300 width=26> </TD></TR><TR style="HEIGHT: 15pt; mso-height-source: userset" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #dbe5f1; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl299 height=20> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl354> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl371> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl371 colSpan=2> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl371> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl371> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl371> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl353> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl353> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl352> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl350> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl351> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl351> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl425> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl350> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl350> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl350> </TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" height=20 vAlign=top width=95 colSpan=2 align=left><v:shapetype id=_x0000_t75 stroked="f" filled="f" path="m@4@5l@4@11@9@11@9@5xe" o



