View Full Version : VBA look for cell next to first blank, copy and paste
gwacouk
10-25-2020, 08:11 AM
Hi there,
I need a vba code that looks in a particular sheet, in a particular column for the first unpopulated cell, then copies the value of the first cell on the right of the blank cell just found, and finally pastes the copied value to the first blank cell in another column in another worksheet.
I have been trying for some time, but not luck, would appreciate you help. Thank you.
I wouldn't look in the Test forum. Try the Excel Help Forum
gwacouk
10-27-2020, 02:31 AM
Thank you
Sub CopyToAnotherPlace()
Dim Src As Range
Dim Dest As Range
Set Src = RightOfFirstBlank(Sheets("Source WorkSheet Name"), Column Number)
Set Dest = NextEmptyCell(Sheets("Destination WorkSheet Name"), Column Number)
Src.Copy Dest
End Sub
Sub CopyToAnotherPlace_2()
RightOfFirstBlank(Source Sht, Column Number).Copy NextEmptyCell(Destination Sht, Column Number)
End Sub
Function RightOfFirstBlank(Sht As WorkSheet, ColumnNumber As Long) As Range
'This uses the sheet, If you have more than a few thousand Rows before any blanks, it would
' be worth it to use an array herein.
Dim Rw As Long
With Sht
For Rw = 1 to .Cells(Rows.Count, ColumnNumber).End(xlUp).Row + 1
If .Cells(Rw, ColumnNumber) = 0
Then Set RightOfFirstBlank = .Cells(Rw, ColumnNumber + 1)
Exit For
End If
Next
End With
End Function
Function NextEmptyCell(Sht As WorkSheet, ColumnNumber As Long) As Range
With Sht
If .Cells(1, ColumnNumber) = 0 Then
Set NextEmptyCell = .Cells(1, ColumnNumber)
Else: Set NextEmptyCell = .Cells(Rows.Count, ColumnNumber).End(xlUp).Offset(1)
End If
End With
End Function
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.