Find Empty Cell and Paste

Here's the thing - i have this code here:
Basically its copying whatever value is found into the worksheet
Removed Questions.

How do i make it so that whenever i copy over the cells it pastes it
into an empty cell on the new worksheet? So it would first search for
the first blank row or cell on the "Removed Questions" - but then also
paste the information into the next blank cell.

The range to search would be ("A:A") on the "removed questions"




Dim rngLookup As String ' Value to search for
Dim rngFound As Range ' Cell rngLookup is found in
Dim firstAddress As String 'Cell address of the first value found
Dim wksDisplayResults As Worksheet ' Output sheet
Dim wksMaster As Worksheet 'Master Questions sheet

Dim ri As Long ' Row Index used to know which row results should
paste into
Dim bContinue As Boolean ' Used to stop find loop

Set wksMaster = Worksheets("Master Questions")
Set wksDisplayResults = Worksheets("Removed Questions")

ri = 2 'Row to begin pasting results

rngLookup = "D" ' Value to search for

'Before beginning loop, copy the header to result sheet
wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")

' Find Lookup Value
With Worksheets("Master Questions").Range("e2:e65000")
Set rngFound = .Find(rngLookup, _
LookIn:=xlValues, _
LookAt:=xlPart, _
MatchCase:=True)
'Return message if value not found
If rngFound Is Nothing Then
MsgBox ("The search item " & rngLookup & " was not
found")
Else
firstAddress = rngFound.Address
bContinue = True

'Continue looping until bcontinue is false
Do While bContinue
'Cut or copy row into result sheet, then increment
the row index
rngFound.EntireRow.Copy wksDisplayResults.Rows(ri)
ri = ri + 1
'Find the next cell containing lookup value
Set rngFound = .FindNext(rngFound)
'If range found is not nothing, then bContinue will
remain true
bContinue = Not rngFound Is Nothing
'Then check to see if rngfound's address is equal
to firstaddress
If bContinue = True Then bContinue =
rngFound.Address <> firstAddress
Loop
End If
End With


End If

Perhaps you could greatly simplify this by using FINDNEXT instead of FIND.
There is a good example in the vba help index


--
Don Guillett
SalesAid Software
[email protected]
"RigasMinho" <[email protected]> wrote in message
news:[email protected]...
> Here's the thing - i have this code here:
> Basically its copying whatever value is found into the worksheet
> Removed Questions.
>
> How do i make it so that whenever i copy over the cells it pastes it
> into an empty cell on the new worksheet? So it would first search for
> the first blank row or cell on the "Removed Questions" - but then also
> paste the information into the next blank cell.
>
> The range to search would be ("A:A") on the "removed questions"
>
>
>
>
> Dim rngLookup As String ' Value to search for
> Dim rngFound As Range ' Cell rngLookup is found in
> Dim firstAddress As String 'Cell address of the first value found
> Dim wksDisplayResults As Worksheet ' Output sheet
> Dim wksMaster As Worksheet 'Master Questions sheet
>
> Dim ri As Long ' Row Index used to know which row results should
> paste into
> Dim bContinue As Boolean ' Used to stop find loop
>
> Set wksMaster = Worksheets("Master Questions")
> Set wksDisplayResults = Worksheets("Removed Questions")
>
> ri = 2 'Row to begin pasting results
>
> rngLookup = "D" ' Value to search for
>
> 'Before beginning loop, copy the header to result sheet
> wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
>
> ' Find Lookup Value
> With Worksheets("Master Questions").Range("e2:e65000")
> Set rngFound = .Find(rngLookup, _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> MatchCase:=True)
> 'Return message if value not found
> If rngFound Is Nothing Then
> MsgBox ("The search item " & rngLookup & " was not
> found")
> Else
> firstAddress = rngFound.Address
> bContinue = True
>
> 'Continue looping until bcontinue is false
> Do While bContinue
> 'Cut or copy row into result sheet, then increment
> the row index
> rngFound.EntireRow.Copy wksDisplayResults.Rows(ri)
> ri = ri + 1
> 'Find the next cell containing lookup value
> Set rngFound = .FindNext(rngFound)
> 'If range found is not nothing, then bContinue will
> remain true
> bContinue = Not rngFound Is Nothing
> 'Then check to see if rngfound's address is equal
> to firstaddress
> If bContinue = True Then bContinue =
> rngFound.Address <> firstAddress
> Loop
> End If
> End With
>
>
> End If
>

Yeah that doesnt help me at all

Now i need to call help desk about this later today arrggg


Don Guillett wrote:
> Perhaps you could greatly simplify this by using FINDNEXT instead of FIND.
> There is a good example in the vba help index
>
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "RigasMinho" <[email protected]> wrote in message
> news:[email protected]...
> > Here's the thing - i have this code here:
> > Basically its copying whatever value is found into the worksheet
> > Removed Questions.
> >
> > How do i make it so that whenever i copy over the cells it pastes it
> > into an empty cell on the new worksheet? So it would first search for
> > the first blank row or cell on the "Removed Questions" - but then also
> > paste the information into the next blank cell.
> >
> > The range to search would be ("A:A") on the "removed questions"
> >
> >
> >
> >
> > Dim rngLookup As String ' Value to search for
> > Dim rngFound As Range ' Cell rngLookup is found in
> > Dim firstAddress As String 'Cell address of the first value found
> > Dim wksDisplayResults As Worksheet ' Output sheet
> > Dim wksMaster As Worksheet 'Master Questions sheet
> >
> > Dim ri As Long ' Row Index used to know which row results should
> > paste into
> > Dim bContinue As Boolean ' Used to stop find loop
> >
> > Set wksMaster = Worksheets("Master Questions")
> > Set wksDisplayResults = Worksheets("Removed Questions")
> >
> > ri = 2 'Row to begin pasting results
> >
> > rngLookup = "D" ' Value to search for
> >
> > 'Before beginning loop, copy the header to result sheet
> > wksMaster.Range("a1").EntireRow.Copy wksDisplayResults.Range("a1")
> >
> > ' Find Lookup Value
> > With Worksheets("Master Questions").Range("e2:e65000")
> > Set rngFound = .Find(rngLookup, _
> > LookIn:=xlValues, _
> > LookAt:=xlPart, _
> > MatchCase:=True)
> > 'Return message if value not found
> > If rngFound Is Nothing Then
> > MsgBox ("The search item " & rngLookup & " was not
> > found")
> > Else
> > firstAddress = rngFound.Address
> > bContinue = True
> >
> > 'Continue looping until bcontinue is false
> > Do While bContinue
> > 'Cut or copy row into result sheet, then increment
> > the row index
> > rngFound.EntireRow.Copy wksDisplayResults.Rows(ri)
> > ri = ri + 1
> > 'Find the next cell containing lookup value
> > Set rngFound = .FindNext(rngFound)
> > 'If range found is not nothing, then bContinue will
> > remain true
> > bContinue = Not rngFound Is Nothing
> > 'Then check to see if rngfound's address is equal
> > to firstaddress
> > If bContinue = True Then bContinue =
> > rngFound.Address <> firstAddress
> > Loop
> > End If
> > End With
> >
> >
> > End If
> >