View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
JLGWhiz[_2_] JLGWhiz[_2_] is offline
external usenet poster
 
Posts: 1,565
Default Fill blanks with find results


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.



"DavidH56" wrote in message
...
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.
--
By persisting in your path, though you forfeit the little, you gain the
great.