use next available cell in row (xltoright)

BRB1983

Board Regular
Joined
Aug 29, 2019
Messages
58
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
8,887
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
58
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
58
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
8,887
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,082,283
Messages
5,364,268
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top