I wonder if somebody can help me out a bit here, please.
I have two stock lists this month and previous month. This month is pasted into columns A5, B5, C5 and the previous month pasted into columns E5, F5, G5, columns A & E are unique code numbers, C & G are the value of the stock, B & F just a description.
What I have been trying to do is copy the values for each unique number & paste them into column H against the row with the corresponding unique number, I can then enter a formula in column I to determine the difference between the two values, or even better just paste the difference in values in column I.
I think I have got the code to work as far as copying the value from the current month, but I am struggling to paste it in the row corresponding to the unique number for the previous month.
Any help is always appreciated
Code below
I have two stock lists this month and previous month. This month is pasted into columns A5, B5, C5 and the previous month pasted into columns E5, F5, G5, columns A & E are unique code numbers, C & G are the value of the stock, B & F just a description.
What I have been trying to do is copy the values for each unique number & paste them into column H against the row with the corresponding unique number, I can then enter a formula in column I to determine the difference between the two values, or even better just paste the difference in values in column I.
I think I have got the code to work as far as copying the value from the current month, but I am struggling to paste it in the row corresponding to the unique number for the previous month.
Any help is always appreciated
Code below
VBA Code:
Sub compareStockValue()
Dim sh As Worksheet, lr As Long, fVal As Range, c As Range
Set sh = Sheets(1) 'Edit sheet name
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
For Each c In sh.Range("E5:E1500") 'Assumes header rows
Set fVal = sh.Range("A5:A" & lr).Find(c.Value, LookIn:=xlValues, LookAt:=xlWhole)
If Not fVal Is Nothing Then
fAdr = fVal.Address
Do
c.Interior.ColorIndex = 6
fVal.Interior.ColorIndex = 6
fVal.Value = c.Value
fVal.Offset(0, 2).Copy 'copying the current month stock value
Set fVal = sh.Range("A5:A" & lr).FindNext(fVal)
Loop While fVal.Address <> fAdr
End If
Next
Application.CutCopyMode = False
End Sub