Fill blanks with find results

[DavidH56]

Hi,

I'm trying to modify this code to look for all blank cells starting with
row3 and fill them in with find results from the data worksheet. This code
works for the first blank cell but stops there. I would like to start with
selecting the first blank cell, looking at the value (text) of the cell blow
it, searching for this value in a long string of text on the data sheet, and
then copying the entire row from the data sheet to the blank cell row. I'm
basically trying to title each group of rows. I would then like to procede
to the next blank cell to do the same until I reach the end of blank cells
with the used range. This is the code I'm starting with.

Sub FindGroupID()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 3
'This step assumes that you have a worksheet named
'Data.
Set wSht = Worksheets("Sheet1")
strToFind = ActiveCell.Offset(1, 0).Value
'Change this range to suit your own needs.
With Worksheets("Data").Range("A1:A100")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

End Sub

Any help that anyone can provide would be greatly appreciated.

 

[JLGWhiz]

Your description is a little confusing.

'for all blank cells starting with row3 and fill them in with find results
from the data worksheet.'

' and then copying the entire row from the data sheet to the blank cell
row'

If the first item is found and the row is copied from "Data" to "Sheet1"
Then there are no more blank cells on row 3. This then infers that you are
really only looking at one column for the blank row. However,

'I would then like to procede to the next blank cell to do the same until I
reach the end of blank cells with the used range. This is the code I'm
starting with.'

this statement then implies that you are making a random search for the
blank cells throughout the used range. Looking at your code gives no
clarification, since it does not indicate how you found the first blank
cell, although, it does indicate that a single column is being used since
you advance one row in the Do loop.

Can you clear this up a little. Are you looking in one column of Sheet1 for
blank cells, and if one is found, search for the data in the cell beneath
the blank cell in Sheets("Data"). For the found cell in "Data", copy the
entire row to the blank cell row in sheet1? If so, what column in Sheet1
needs to be searched for blanks.

 

[JLGWhiz]

I took a guess and went ahead on the asumption that
you were looking in column A for the blank cells.
I only checked to see that the code compiled, so
you need to test it before installing it for permanent use.



Sub FindGroupID()
Dim lr As Long
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").Range("A3:A" & lr)
If c.Value = "" Then
strToFind = c.Offset(1, 0).Value
Set wSht = Worksheets("Sheet1")
'Change this range to suit your own needs.
With Worksheets("Data").Range("A1:A100")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
rngC.EntireRow.Copy wSht.Cells(c.Row, 1)
End If
End With
End If


Next

End Sub

Your description is a little confusing.

'for all blank cells starting with row3 and fill them in with find
results from the data worksheet.'

' and then copying the entire row from the data sheet to the blank cell
row'

If the first item is found and the row is copied from "Data" to "Sheet1"
Then there are no more blank cells on row 3. This then infers that you
are really only looking at one column for the blank row. However,

'I would then like to procede to the next blank cell to do the same until
I reach the end of blank cells with the used range. This is the code I'm
starting with.'

this statement then implies that you are making a random search for the
blank cells throughout the used range. Looking at your code gives no
clarification, since it does not indicate how you found the first blank
cell, although, it does indicate that a single column is being used since
you advance one row in the Do loop.

Can you clear this up a little. Are you looking in one column of Sheet1
for blank cells, and if one is found, search for the data in the cell
beneath the blank cell in Sheets("Data"). For the found cell in "Data",
copy the entire row to the blank cell row in sheet1? If so, what column
in Sheet1 needs to be searched for blanks.

 

[Simon Lloyd]

Are you looking to fill all blank cells in a range with a single entry
or a different entry in each blank?

Hi,

I'm trying to modify this code to look for all blank cells starting
with
row3 and fill them in with find results from the data worksheet. This
code
works for the first blank cell but stops there. I would like to start
with
selecting the first blank cell, looking at the value (text) of the cell
blow
it, searching for this value in a long string of text on the data
sheet, and
then copying the entire row from the data sheet to the blank cell row.
I'm
basically trying to title each group of rows. I would then like to
procede
to the next blank cell to do the same until I reach the end of blank
cells
with the used range. This is the code I'm starting with.

Sub FindGroupID()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 3
'This step assumes that you have a worksheet named
'Data.
Set wSht = Worksheets("Sheet1")
strToFind = ActiveCell.Offset(1, 0).Value
'Change this range to suit your own needs.
With Worksheets("Data").Range("A1:A100")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With

End Sub

Any help that anyone can provide would be greatly appreciated.

--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)

 

[DavidH56]

Thank you both for your responses. Yes JLGWhiz this works perfectly. Sorry
I was vague in my description of what I wanted.

Thank you both for your time and attention.
--
By persisting in your path, though you forfeit the little, you gain the
great.

I took a guess and went ahead on the asumption that
you were looking in column A for the blank cells.
I only checked to see that the code compiled, so
you need to test it before installing it for permanent use.



Sub FindGroupID()
Dim lr As Long
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
lr = Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
For Each c In Sheets("Sheet1").Range("A3:A" & lr)
If c.Value = "" Then
strToFind = c.Offset(1, 0).Value
Set wSht = Worksheets("Sheet1")
'Change this range to suit your own needs.
With Worksheets("Data").Range("A1:A100")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
rngC.EntireRow.Copy wSht.Cells(c.Row, 1)
End If
End With
End If


Next

End Sub