Prev: Find word copy and paste
Next: XL file opened using automation - how close?
From: sebastico on 27 May 2010 17:42
Hello

I apologize. In my previous question I did not include the code form Ron
that I'm testing.

Excel 2003
I have a sheet with six columns with 700 rows each with text. I have to red
all rows to find a word and copy and paste in the same cell of column b. This
is really heavy task and the risk of error is a problem. I 'm looking for a
code to do this task.

I have been trying the code from Ron de Bruin I found in this forum but when
I run the code this message is displayed:
Run-time error :1004:
Application defined or object defined error
I am learning VBA, I don't know hot to fix the error.

First I have been trying the search with in one column

I have a Sheet with 6 columns (F,G,H,I) with 700 rows.
I need a code to do this:
1. Read F:1 if find the word Apple, Then copy and paste the word in cell B:1
2. Go to F:2 if find the word Apple, Then copy and paste the word in cell B:2
3. Same until F700

If possible do the same application in Columns G, H, I

This is the code form ROn de Bruin

Sub Find_First()
Dim FindString As String
Dim rng As Range
FindString = "Apple"
If Trim(FindString) <> "" Then
With Sheets("Sheet1").Range("A:A")
Set rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not rng Is Nothing Then
rng.Offset(0, 1).Copy rng.Parent.Range("D2")
Else
MsgBox "Nothing found"

End If
End With
End If
End Sub

Your help is welcome
From: Don Guillett on 27 May 2010 18:08
If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguillett(a)gmail.com
"sebastico" <sebastico(a)discussions.microsoft.com> wrote in message
news:097EF390-7AE5-4203-8842-27AE3886D403(a)microsoft.com...
> Hello
>
> I apologize. In my previous question I did not include the code form Ron
> that I'm testing.
>
> Excel 2003
> I have a sheet with six columns with 700 rows each with text. I have to
> red
> all rows to find a word and copy and paste in the same cell of column b.
> This
> is really heavy task and the risk of error is a problem. I 'm looking for
> a
> code to do this task.
>
> I have been trying the code from Ron de Bruin I found in this forum but
> when
> I run the code this message is displayed:
> Run-time error :1004:
> Application defined or object defined error
> I am learning VBA, I don't know hot to fix the error.
>
> First I have been trying the search with in one column
>
> I have a Sheet with 6 columns (F,G,H,I) with 700 rows.
> I need a code to do this:
> 1. Read F:1 if find the word Apple, Then copy and paste the word in cell
> B:1
> 2. Go to F:2 if find the word Apple, Then copy and paste the word in cell
> B:2
> 3. Same until F700
>
> If possible do the same application in Columns G, H, I
>
> This is the code form ROn de Bruin
>
> Sub Find_First()
> Dim FindString As String
> Dim rng As Range
> FindString = "Apple"
> If Trim(FindString) <> "" Then
> With Sheets("Sheet1").Range("A:A")
> Set rng = .Find(What:=FindString, _
> After:=.Cells(.Cells.Count), _
> LookIn:=xlValues, _
> LookAt:=xlWhole, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not rng Is Nothing Then
> rng.Offset(0, 1).Copy rng.Parent.Range("D2")
> Else
> MsgBox "Nothing found"
>
> End If
> End With
> End If
> End Sub
>
> Your help is welcome

 | 
Pages: 1
Prev: Find word copy and paste
Next: XL file opened using automation - how close?