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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
9,527
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
9,527
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,084,744
Messages
5,379,588
Members
401,614
Latest member
priokatm

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top