Microsoft Office Forums - View Single Post

Suggy · 05-17-2017, 01:28 PM

Quote:

Originally Posted by jolivanes

I have tried several times to attach a workbook but no luck at all.

I have renamed the first sheet (Spelling)
I have changed the header in Column D (Spelling)

Instead of transferring every time you put an "y" or "Y" somewhere in column E, put an y in Column E in all the rows that need transferring and after you've done them all, click on the "Transfer" button.

You might look into the numbering of the colored cells in Column A. Same numbers might give wrong answers (rows 26 and 27 as well as rows 28 and 29).
You could put an A or B at the end maybe.

Code:

Sub Transfer()
Dim i As Long, a As String
Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Deliveries")
Set sh2 = Sheets("Stock")
Application.ScreenUpdating = False
    For i = 2 To sh1.Cells(sh1.Rows.Count, 1).End(xlUp).Row
        If sh1.Cells(i, 5).Value = "y" Then
            a = sh1.Cells(i, 1).Value
                sh2.Cells(sh2.Columns(3).Find(a, , , 1).Row, 7).Value = sh1.Cells(i, 5).Offset(, -1).Value
            sh1.Cells(i, 5).Offset(, -1).ClearContents
        End If
    Next i
Application.ScreenUpdating = True
End Sub

Hi Typos corrected (Thanks) I have put the macro in the spread sheet and added an extra line
sh1.Cells(i, 5).ClearContents
To clear the y from sheet1 as well as the number but when I click the button it replaces the stock on sheet2 instead of adding it to the stock that's already there. I don't have a clue what to do to add the 2 figures together so I would be most grateful if you could take another look for me?

Cheers

Ian