Populate First Empty Cell In each Row with Text

[Sam via OfficeKB.com]

Hi All,

Can anyone provide a VBA script that can expand on the following code to
automate looping through Row number 2 to Row number 60: populating each Row's
First Empty Cell with data from two non-adjacent columns (BG2:BG60 and BI2:
BI60). The data in BG2 and BI2 should be concatenated and placed in the First
Empty Cell of that Row (number 2) and the same for Row number three to Row
number 60 - concatenating Column's BG and BI Row data accordingly.

Current Code for First Row (Row number 2):

Rows("2:2").Find(What:="", After:=[BR2], SearchOrder:=xlByColumns).Select
Selection.Value = Range("BG2") & " " & Range("BI2")

Thanks
Sam

 

[Mike Fogleman]

Sam, This should be pretty close to what you want:

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c],
SearchOrder:=xlByRows).Select
Selection.Value = Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

 

[Mike Fogleman]

Sorry, the cells.find line got word wrapped in the first post.

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c], _
SearchOrder:=xlByRows).Select
Selection.Value = Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

Sam, This should be pretty close to what you want:

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c],
SearchOrder:=xlByRows).Select
Selection.Value = Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

Hi All,

Can anyone provide a VBA script that can expand on the following code to
automate looping through Row number 2 to Row number 60: populating each
Row's
First Empty Cell with data from two non-adjacent columns (BG2:BG60 and
BI2:
BI60). The data in BG2 and BI2 should be concatenated and placed in the
First
Empty Cell of that Row (number 2) and the same for Row number three to
Row
number 60 - concatenating Column's BG and BI Row data accordingly.

Current Code for First Row (Row number 2):

Rows("2:2").Find(What:="", After:=[BR2], SearchOrder:=xlByColumns).Select
Selection.Value = Range("BG2") & " " & Range("BI2")

Thanks
Sam

 

[Mike Fogleman]

Also, if you want to skip the cell selection each time, then try this:

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c], _
SearchOrder:=xlByRows).Value = _
Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

Sorry, the cells.find line got word wrapped in the first post.

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c], _
SearchOrder:=xlByRows).Select
Selection.Value = Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

Sam, This should be pretty close to what you want:

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c],
SearchOrder:=xlByRows).Select
Selection.Value = Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub

Mike F

Hi All,

Can anyone provide a VBA script that can expand on the following code to
automate looping through Row number 2 to Row number 60: populating each
Row's
First Empty Cell with data from two non-adjacent columns (BG2:BG60 and
BI2:
BI60). The data in BG2 and BI2 should be concatenated and placed in the
First
Empty Cell of that Row (number 2) and the same for Row number three to
Row
number 60 - concatenating Column's BG and BI Row data accordingly.

Current Code for First Row (Row number 2):

Rows("2:2").Find(What:="", After:=[BR2],
SearchOrder:=xlByColumns).Select
Selection.Value = Range("BG2") & " " & Range("BI2")

Thanks
Sam

 

[Sam via OfficeKB.com]

Hi Mike,

Thank you so much for your assistance. The code works Brilliantly!

Cheers,
Sam

Mike Fogleman wrote
Also, if you want to skip the cell selection each time, then try this:

Sub test()
Dim i As Long
Dim c As Range
For i = 2 To 60
Set c = Range("BR" & i)
Rows(i & ":" & i).Cells.Find(What:="", After:=[c], _
SearchOrder:=xlByRows).Value = _
Range("BG" & i) & " " & Range("BI" & i)
Next i
End Sub