Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill blanks with find results
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill blanks with find results
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 "JLGWhiz" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill blanks with find results
Are you looking to fill all blank cells in a range with a single entry or a different entry in each blank? DavidH56;393452 Wrote: 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. -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109954 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fill blanks with find results
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. "JLGWhiz" wrote: 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 "JLGWhiz" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to fill up blanks | Excel Discussion (Misc queries) | |||
Clear if "#N/A" and Find End of Range, Fill Blanks | Excel Programming | |||
Fill in the blanks!! | Excel Programming | |||
Fill in the blanks | Excel Programming | |||
Fill in the blanks | Excel Programming |