CODEWITHSUNDEEP
excelfor-loopvba
Vigmo10
Vigmo10

Reputation: 49

I have written the code below in VBA (Excel), but it gives me the wrong result?

I have written this code in VBA:

Sub getdata()

    Dim x As Integer, y As Integer
    Dim rcell As Range

    Sheets("Material Data").Select

    With Sheets("Material Data")
        y = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    For Each rcell In Range("C10:C62")
        If rcell > 1 Then rcell.Copy
        Sheets("Sheet1").Range("A" & y).PasteSpecial xlPasteValues
        y = y + 1
    Next rcell

End Sub

The problem with this code is that in the C10:C62 range, there are only 6 values greater than 1 (3 of them is 65 and the other 3 is 60). But this somehow pastes 53 values in sheet1 and 24 of them is 65 and rest 60. I have no idea where is it getting the 53 values from.

Upvotes: 0

Views: 28

Answers (1)

Maximilian Peters
Maximilian Peters

Reputation: 31709

You only want to paste your values when they are above 1, but your if-then statement only includes the Copy operation, but your paste operation is executed for each cell. Try changing your code as follows:

If rcell > 1 Then 
    rcell.Copy
    Sheets("Sheet1").Range("A" & y).PasteSpecial xlPasteValues
    y = y + 1
End If

Upvotes: 1

Related Questions