Find Empty Cell and Paste


R

RigasMinho

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
 
Ad

Advertisements

D

Don Guillett

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

Advertisements

R

RigasMinho

Yeah that doesnt help me at all

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top