use next available cell in row (xltoright)

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
61
i need help getting this code to paste in next empty cell to right.
the code only paste in column B. i need-if column B is not blank then use next cell to right.
Code:
Dim ThisCell1 As Range
Dim ThisCell2 As Range
Application.ScreenUpdating = False
    For Each ThisCell1 In Sheets("sheet1").Range("A1:A1089")
        For Each ThisCell2 In Sheets("sheet0").Range("b2:b3392")
            If ThisCell1.Value = ThisCell2.Value Then
                ThisCell2.Offset(0, 1).Copy
                ThisCell1.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
                Exit For
             End If
        Next ThisCell2
    Next ThisCell1
Application.ScreenUpdating = True
 

Some videos you may like

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,189
Office Version
2007
Platform
Windows
Try this

Code:
Sub next_available_cell()
  Dim Cell1 As Range, f As Range
  Application.ScreenUpdating = False
  For Each Cell1 In Sheets("sheet1").Range("A1", Sheets("sheet1").Range("A" & Rows.Count).End(xlUp))
    Set f = Sheets("sheet0").Range("b2:b3392").Find(Cell1, , xlValues, xlWhole)
    If Not f Is Nothing Then Cell1.Cells(1, Cell1.Cells(1, Columns.Count).End(xlToLeft).Column + 1).Value = f.Offset(, 1).Value
  Next
End Sub
 

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
61
I did this, it works, but it is slow.
Code:
Dim ThisCell1 As RangeDim ThisCell2 As Range
Dim LCol As Long
    LCol = Cells(1, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
    For Each ThisCell1 In Sheets("sheet1").Range("A1:A1089")
        For Each ThisCell2 In Sheets("sheet0").Range("b2:b3392")
            If ThisCell1.Value = ThisCell2.Value Then
                ThisCell2.Offset(0, 1).Copy
                If Not ThisCell1.Offset(0, 1) = "" Then
                ThisCell1(LCol, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial
                End If
                ThisCell1.Offset(0, 1).PasteSpecial Paste:=xlPasteValues
             End If
        Next ThisCell2
    Next ThisCell1
Application.ScreenUpdating = True
 

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
61
that one does not paste in the next available cell to right. just the ONE on the right.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
10,189
Office Version
2007
Platform
Windows
I do not understand well.
Do you want the next available cell of the same row or the next available cell according to the row 1?

Try this

Code:
Sub next_available_cell()
  Dim Cell1 As Range, f As Range, lc As Long
  Application.ScreenUpdating = False
  lc = Sheets("sheet1").Cells(1, Columns.Count).End(xlToLeft).Column + 1
  For Each Cell1 In Sheets("sheet1").Range("A1", Sheets("sheet1").Range("A" & Rows.Count).End(xlUp))
    Set f = Sheets("sheet0").Range("b2:b3392").Find(Cell1, , xlValues, xlWhole)
    If Not f Is Nothing Then Cell1.Cells(1, lc).Value = f.Offset(, 1).Value
  Next
End Sub
 

Forum statistics

Threads
1,089,278
Messages
5,407,345
Members
403,135
Latest member
becca_832313

This Week's Hot Topics

Top