CODEWITHSUNDEEP
vbaexcel
vickynorth
vickynorth

Reputation: 23

Find first empty cell in Col U & copy/paste adjacent value from Col O

I've been struggling with what I thought would be a very easy/simple piece of code.

I would like to create a loop to find the first empty cell in Col U & copy the adjacent value in Col O (number representing month) to the empty cell in Col U, but convert the number to month name.

I want this loop to run in the worksheet "Adjustments" only, automatically transposing the month name to Col U (from # in Col O) as new entries are made.

So far I've managed this:

Sub MonthName()
    Dim ws As Worksheet
    Set ws = Sheets("Adjustments")
    For Each cell In ws.Columns(21).Cells
         If Len(cell) = 0 Then cell.Offset(0, -6).Copy: Exit For
    Next cell
End Sub

enter image description here

Really appreciate any help!

Upvotes: 2

Views: 113

Answers (2)

brettdj
brettdj

Reputation: 55682

Suggest you use Find to get the first blank cell rather than loop.

If you want this to run automatically you could trigger it from a sheet change event (when a particular range of cells is updated, or selected, or calculated etc). 

 Dim ws As Worksheet
 Dim rng1 As Range

 Set ws = Sheets("Adjustments") 
 Set rng1 = ws.Columns("U").Find("", ws.Cells(ws.Rows.Count, "U"), xlFormulas, xlWhole)
 If Not rng1 Is Nothing Then rng1.Value = Format(ws.Cells(rng1.Row, "O") + 1, "mmm")

updated

  • right click your sheet called adjustments
  • View Code
  • copy and paste in the code below

code 

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Intersect(Columns("O"), Target)
If rng1 Is Nothing Then Exit Sub

Set rng1 = Columns("U").Find("", Cells(Rows.Count, "U"), xlFormulas, xlWhole)
If Not rng1 Is Nothing Then
    Application.EnableEvents = False
    rng1.Value = Format(Cells(rng1.Row, "O") + 1, "mmm")
    Application.EnableEvents = True
End If
End Sub

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

If Len(Trim(cell.Value)) = 0 Then cell.Value = MonthName(cell.Offset(, -6).Value)

And remove that Exit For IF you want to change all empty cells at columns U not only the first one. Otherwise keep it.

Upvotes: 1

Related Questions