Consulting

Results 1 to 4 of 4

Thread: VBA look for cell next to first blank, copy and paste

  1. #1

    Lightbulb VBA look for cell next to first blank, copy and paste

    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.

  2. #2
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    I wouldn't look in the Test forum. Try the Excel Help Forum
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  3. #3
    Thank you

  4. #4
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •