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?

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?

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?

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?

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?