CODEWITHSUNDEEP
excelvba
Abhijeet
Abhijeet

Reputation: 269

Not able to operate on dynamic non empty cells

i have to sort data from sheet1 to sheet2 with reference to non-empty cell in column A.Sheet1 data and Sheet2 data

i have written code for it as below:

Sub polo()
Dim lastrow As Long
lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
j = 2
For i = 1 To lastrow
If Sheets("Sheet1").Cells(i, 1).Value = "" Then
Sheets("Sheet2").Cells(j, 2).Value = Sheets("Sheet1").Cells(i, 1).Offset(, 1).Value
Sheets("Sheet2").Cells(j, 4).Value = Sheets("Sheet1").Cells(i - 1, 2).Value
j = j + 1
End If
Next i
End Sub

But the problem is, i am getting result as in column D of sheet2. I want result as shown in column E.

Please help.

Upvotes: 0

Views: 16

Answers (1)

Rory
Rory

Reputation: 34045

Try this version:

Sub polo()
    Dim lastrow               As Long
    Dim sTemp as String
    lastrow = Sheets("Sheet1").Range("B" & Rows.Count).End(xlUp).Row
    j = 2
    For i = 1 To lastrow
        If Sheets("Sheet1").Cells(i, 1).Value = "" Then
            Sheets("Sheet2").Cells(j, 2).Value = Sheets("Sheet1").Cells(i, 1).Offset(, 1).Value
            Sheets("Sheet2").Cells(j, 4).Value = stemp
            j = j + 1
        Else
            stemp = Sheets("Sheet1").Cells(i, 2).Value
        End If
    Next i
End Sub

Upvotes: 2

Related Questions