Find Copy and Paste
I have this:
Private Sub CommandButton1_Click() Dim lngfindcells As Long 'Set counter up for Sheet1 Dim lngdestcount As Long 'Set counter up for destination cells inSheet2 Dim x As Long 'Set counter = 1 so destination values on Sheet2 begins at row 1 lngdestcount = 1 'Cycle through every cell in Sheet1 column A, starting at row 1 For lngfindcells = 1 To 10 If Worksheets("Master Questions").Cells(lngdestcount, 1) = "" Then 'Do Nothing Else 'If cell on Sheet1 has a value, copy it and paste it into Sheet2, beginning in column A, row 1 Worksheets("Master Questions").Range("B" & lngfindcells & ":B" & lngfindcells).Cells.Find("A").Offset(0, -1).Copy Destination:=Worksheets("Output").Range("A" & lngdestcount) 'Increment counter so next value copied will be belowprevious one on Sheet2 lngdestcount = lngdestcount + 1 End If Next End Sub Where Master questions has Column A with data Column B with A B or C in the cell as a value it outputs only the first cell in column B with the value "A" but doesnt move onto the next cell Any Ideas? |
Find Copy and Paste
This will find all instances of "A" in column B on Master Questions and paste
them in A2 on sheet Output... Public Sub FindStuff() Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A2") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("B") Set rngFound = rngToSearch.Find(What:="A", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngFoundAll.Offset(0, -1).Copy rngDestination End If End Sub -- HTH... Jim Thomlinson "RigasMinho" wrote: I have this: Private Sub CommandButton1_Click() Dim lngfindcells As Long 'Set counter up for Sheet1 Dim lngdestcount As Long 'Set counter up for destination cells inSheet2 Dim x As Long 'Set counter = 1 so destination values on Sheet2 begins at row 1 lngdestcount = 1 'Cycle through every cell in Sheet1 column A, starting at row 1 For lngfindcells = 1 To 10 If Worksheets("Master Questions").Cells(lngdestcount, 1) = "" Then 'Do Nothing Else 'If cell on Sheet1 has a value, copy it and paste it into Sheet2, beginning in column A, row 1 Worksheets("Master Questions").Range("B" & lngfindcells & ":B" & lngfindcells).Cells.Find("A").Offset(0, -1).Copy Destination:=Worksheets("Output").Range("A" & lngdestcount) 'Increment counter so next value copied will be belowprevious one on Sheet2 lngdestcount = lngdestcount + 1 End If Next End Sub Where Master questions has Column A with data Column B with A B or C in the cell as a value it outputs only the first cell in column B with the value "A" but doesnt move onto the next cell Any Ideas? |
Find Copy and Paste
Instead of copying the cell over is there a way to grab the whole row
where the cell is found? and paste over teh whole row instead of just teh cell? Jim Thomlinson wrote: This will find all instances of "A" in column B on Master Questions and paste them in A2 on sheet Output... Public Sub FindStuff() Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A2") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("B") Set rngFound = rngToSearch.Find(What:="A", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngFoundAll.Offset(0, -1).Copy rngDestination End If End Sub -- HTH... Jim Thomlinson "RigasMinho" wrote: I have this: Private Sub CommandButton1_Click() Dim lngfindcells As Long 'Set counter up for Sheet1 Dim lngdestcount As Long 'Set counter up for destination cells inSheet2 Dim x As Long 'Set counter = 1 so destination values on Sheet2 begins at row 1 lngdestcount = 1 'Cycle through every cell in Sheet1 column A, starting at row 1 For lngfindcells = 1 To 10 If Worksheets("Master Questions").Cells(lngdestcount, 1) = "" Then 'Do Nothing Else 'If cell on Sheet1 has a value, copy it and paste it into Sheet2, beginning in column A, row 1 Worksheets("Master Questions").Range("B" & lngfindcells & ":B" & lngfindcells).Cells.Find("A").Offset(0, -1).Copy Destination:=Worksheets("Output").Range("A" & lngdestcount) 'Increment counter so next value copied will be belowprevious one on Sheet2 lngdestcount = lngdestcount + 1 End If Next End Sub Where Master questions has Column A with data Column B with A B or C in the cell as a value it outputs only the first cell in column B with the value "A" but doesnt move onto the next cell Any Ideas? |
Find Copy and Paste
Instead of copying the cell over is there a way to grab the whole row
where the cell is found? and paste over teh whole row instead of just teh cell? Jim Thomlinson wrote: This will find all instances of "A" in column B on Master Questions and paste them in A2 on sheet Output... Public Sub FindStuff() Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A2") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("B") Set rngFound = rngToSearch.Find(What:="A", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngFoundAll.Offset(0, -1).Copy rngDestination End If End Sub -- HTH... Jim Thomlinson "RigasMinho" wrote: I have this: Private Sub CommandButton1_Click() Dim lngfindcells As Long 'Set counter up for Sheet1 Dim lngdestcount As Long 'Set counter up for destination cells inSheet2 Dim x As Long 'Set counter = 1 so destination values on Sheet2 begins at row 1 lngdestcount = 1 'Cycle through every cell in Sheet1 column A, starting at row 1 For lngfindcells = 1 To 10 If Worksheets("Master Questions").Cells(lngdestcount, 1) = "" Then 'Do Nothing Else 'If cell on Sheet1 has a value, copy it and paste it into Sheet2, beginning in column A, row 1 Worksheets("Master Questions").Range("B" & lngfindcells & ":B" & lngfindcells).Cells.Find("A").Offset(0, -1).Copy Destination:=Worksheets("Output").Range("A" & lngdestcount) 'Increment counter so next value copied will be belowprevious one on Sheet2 lngdestcount = lngdestcount + 1 End If Next End Sub Where Master questions has Column A with data Column B with A B or C in the cell as a value it outputs only the first cell in column B with the value "A" but doesnt move onto the next cell Any Ideas? |
Find Copy and Paste
Never mind i got it:
rngFoundAll.EntireRow.Copy rngDestination RigasMinho wrote: Instead of copying the cell over is there a way to grab the whole row where the cell is found? and paste over teh whole row instead of just teh cell? Jim Thomlinson wrote: This will find all instances of "A" in column B on Master Questions and paste them in A2 on sheet Output... Public Sub FindStuff() Dim wksToSearch As Worksheet Dim rngToSearch As Range Dim rngFound As Range Dim rngFoundAll As Range Dim strFirstAddress As String Dim wksDestination As Worksheet Dim rngDestination As Range Set wksDestination = Sheets("Output") Set rngDestination = wksDestination.Range("A2") Set wksToSearch = Sheets("Master Questions") Set rngToSearch = wksToSearch.Columns("B") Set rngFound = rngToSearch.Find(What:="A", _ LookIn:=xlFormulas, _ LookAt:=xlWhole, _ MatchCase:=False) If rngFound Is Nothing Then MsgBox "Sorry. Not found" Else Set rngFoundAll = rngFound strFirstAddress = rngFound.Address Do Set rngFoundAll = Union(rngFound, rngFoundAll) Set rngFound = rngToSearch.FindNext(rngFound) Loop Until rngFound.Address = strFirstAddress rngFoundAll.Offset(0, -1).Copy rngDestination End If End Sub -- HTH... Jim Thomlinson "RigasMinho" wrote: I have this: Private Sub CommandButton1_Click() Dim lngfindcells As Long 'Set counter up for Sheet1 Dim lngdestcount As Long 'Set counter up for destination cells inSheet2 Dim x As Long 'Set counter = 1 so destination values on Sheet2 begins at row 1 lngdestcount = 1 'Cycle through every cell in Sheet1 column A, starting at row 1 For lngfindcells = 1 To 10 If Worksheets("Master Questions").Cells(lngdestcount, 1) = "" Then 'Do Nothing Else 'If cell on Sheet1 has a value, copy it and paste it into Sheet2, beginning in column A, row 1 Worksheets("Master Questions").Range("B" & lngfindcells & ":B" & lngfindcells).Cells.Find("A").Offset(0, -1).Copy Destination:=Worksheets("Output").Range("A" & lngdestcount) 'Increment counter so next value copied will be belowprevious one on Sheet2 lngdestcount = lngdestcount + 1 End If Next End Sub Where Master questions has Column A with data Column B with A B or C in the cell as a value it outputs only the first cell in column B with the value "A" but doesnt move onto the next cell Any Ideas? |
All times are GMT +1. The time now is 06:12 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com