Thread: Populate First Empty Cell In each Row with Text

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1

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
"Sam via OfficeKB.com" <u4102@uwe> wrote in message
news:6193e1aa66af0@uwe...
> 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
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200606/1

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

"Mike Fogleman" <mikefogleman@insightbb.com> wrote in message
news:rOOdnQ8gqt1etRbZnZ2dnUVZ_vOdnZ2d@insightbb.com...
> 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
> "Sam via OfficeKB.com" <u4102@uwe> wrote in message
> news:6193e1aa66af0@uwe...
>> 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
>>
>> --
>> Message posted via OfficeKB.com
>> http://www.officekb.com/Uwe/Forums.a...mming/200606/1
>
>

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
"Mike Fogleman" <mikefogleman@insightbb.com> wrote in message
news:ZNedndn7joZdtBbZnZ2dnUVZ_t6dnZ2d@insightbb.com...
> 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
>
> "Mike Fogleman" <mikefogleman@insightbb.com> wrote in message
> news:rOOdnQ8gqt1etRbZnZ2dnUVZ_vOdnZ2d@insightbb.com...
>> 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
>> "Sam via OfficeKB.com" <u4102@uwe> wrote in message
>> news:6193e1aa66af0@uwe...
>>> 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
>>>
>>> --
>>> Message posted via OfficeKB.com
>>> http://www.officekb.com/Uwe/Forums.a...mming/200606/1
>>
>>
>
>

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

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200606/1