find replace
 

Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at different
rows/coulums.
Is there any formula/vb code wherein it can find above words and replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell = Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and replace
with mentioned words against it.

rgds


--
rgds

find replace
 

thanks a lot Bob for help. I will try using this.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell = Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and replace
with mentioned words against it.

rgds


--
rgds

find replace
 

hi,

when i copied this code in vb and tried executing the macro, it is showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on vb code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell = Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Hi

The problem is the wrapping created by the NG reader you are using where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

hi

now the macro is not showing any error.however, it is not giving any solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not just
part of the value, which allows the routine to complete i=4 and move on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job much
more concisely. I (being less experienced than Bob) find it easier to do
this so I can see the values clearly when I am stepping through the code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Hi Roger/Bob,

This is a great help.Your contribution is really outstanding.
You deserve maximum ratings for ur work.
Thanks a million.

can u advice me what training/course (relatively short term) do i need to
undergo if I wish to learn programming.

Rgds
Amit

--
rgds


"Roger Govier" wrote:

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not just
part of the value, which allows the routine to complete i=4 and move on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job much
more concisely. I (being less experienced than Bob) find it easier to do
this so I can see the values clearly when I am stepping through the code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Amit,

I think Roger will be of a mind with me in saying that I (we?) don't know of
good training courses, but that the best way is to get a good book, and plug
into these forums and watch the questions. Even if you don't have a problem,
read other's questions and try some of the things. What level would you
describe yourself at?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Hi Roger/Bob,

This is a great help.Your contribution is really outstanding.
You deserve maximum ratings for ur work.
Thanks a million.

can u advice me what training/course (relatively short term) do i need to
undergo if I wish to learn programming.

Rgds
Amit

--
rgds


"Roger Govier" wrote:

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not just
part of the value, which allows the routine to complete i=4 and move on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job much
more concisely. I (being less experienced than Bob) find it easier to do
this so I can see the values clearly when I am stepping through the code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

Hi Bob,

I am basically from finance background with proficiency in excel upto
creating macros.As of now, I have no programming level exp.But i am looking
for something
which can help me in compiling programs.
Which book/author u wud recommend.

--
rgds
amit

"Bob Phillips" wrote:

Amit,

I think Roger will be of a mind with me in saying that I (we?) don't know of
good training courses, but that the best way is to get a good book, and plug
into these forums and watch the questions. Even if you don't have a problem,
read other's questions and try some of the things. What level would you
describe yourself at?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Hi Roger/Bob,

This is a great help.Your contribution is really outstanding.
You deserve maximum ratings for ur work.
Thanks a million.

can u advice me what training/course (relatively short term) do i need to
undergo if I wish to learn programming.

Rgds
Amit

--
rgds


"Roger Govier" wrote:

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not just
part of the value, which allows the routine to complete i=4 and move on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job much
more concisely. I (being less experienced than Bob) find it easier to do
this so I can see the values clearly when I am stepping through the code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i, "A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

You usually can rely on John Walkenbach. I believe he does a VBA for
Dummies, and Power Programming books, so go to a bookshop, and look and see
if one suits.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Hi Bob,

I am basically from finance background with proficiency in excel upto
creating macros.As of now, I have no programming level exp.But i am
looking
for something
which can help me in compiling programs.
Which book/author u wud recommend.

--
rgds
amit

"Bob Phillips" wrote:

Amit,

I think Roger will be of a mind with me in saying that I (we?) don't know
of
good training courses, but that the best way is to get a good book, and
plug
into these forums and watch the questions. Even if you don't have a
problem,
read other's questions and try some of the things. What level would you
describe yourself at?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"spaceage" wrote in message
...
Hi Roger/Bob,

This is a great help.Your contribution is really outstanding.
You deserve maximum ratings for ur work.
Thanks a million.

can u advice me what training/course (relatively short term) do i need
to
undergo if I wish to learn programming.

Rgds
Amit

--
rgds


"Roger Govier" wrote:

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not
just
part of the value, which allows the routine to complete i=4 and move
on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values
from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job
much
more concisely. I (being less experienced than Bob) find it easier to
do
this so I can see the values clearly when I am stepping through the
code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it
is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge
on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count,
TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find
located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words
and
replace
with mentioned words against it.

rgds


--
rgds

find replace
 

thank you, Bob for ur support and guidance.
--
rgds
amit

"Bob Phillips" wrote:

You usually can rely on John Walkenbach. I believe he does a VBA for
Dummies, and Power Programming books, so go to a bookshop, and look and see
if one suits.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"spaceage" wrote in message
...
Hi Bob,

I am basically from finance background with proficiency in excel upto
creating macros.As of now, I have no programming level exp.But i am
looking
for something
which can help me in compiling programs.
Which book/author u wud recommend.

--
rgds
amit

"Bob Phillips" wrote:

Amit,

I think Roger will be of a mind with me in saying that I (we?) don't know
of
good training courses, but that the best way is to get a good book, and
plug
into these forums and watch the questions. Even if you don't have a
problem,
read other's questions and try some of the things. What level would you
describe yourself at?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

"spaceage" wrote in message
...
Hi Roger/Bob,

This is a great help.Your contribution is really outstanding.
You deserve maximum ratings for ur work.
Thanks a million.

can u advice me what training/course (relatively short term) do i need
to
undergo if I wish to learn programming.

Rgds
Amit

--
rgds


"Roger Govier" wrote:

Hi

The problem is the program is going into an interminable loop.
By the time it gets to row 4 of sheet1 (when i=4), the values have
already been changed to USD.
Searching for US, finds USD and changes it to USD and so on ad
infinitum.

I have modified Bob's code to make it look at the whole of USD, not
just
part of the value, which allows the routine to complete i=4 and move
on
through the rest of its iterations.

Public Sub ProcessData2()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String, Value1 As String, value2 As String
On Error Resume Next
With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count, TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow
Value1 = Sheets("Sheet1").Cells(i, "A").Value
value2 = Sheets("Sheet1").Cells(i, "B").Value

With Worksheets("Sheet2")

Set cell = Nothing
Set cell = Worksheets("Sheet2").UsedRange _
.Find(What:=Value1, _
Lookat:=xlWhole, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False)

If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = value2
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing
End With
Next i

End With

End Sub

My routine is a little longer than Bob's, as I have read the values
from
Sheet1 into Value1 and Value2, whereas Bob was doing the whole job
much
more concisely. I (being less experienced than Bob) find it easier to
do
this so I can see the values clearly when I am stepping through the
code
to see if I have got it right.

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi

now the macro is not showing any error.however, it is not giving any
solution.
the files maintains status quo.

can u help.


Sheet 1 data
Find Replace with
krw usd
**US$ USD
*US$ USD
US USD
BRITISH GBP

Sheet2 data(Part Selection)
**US$ US EXCHANGES** *US$
TOTAL EQUITY 6792937.25 2558296.2
** JAPANESE YEN **
TOTAL EQUITY 241,062,029DR 826982.5
** S AFRICAN RAND
TOTAL EQUITY 3,463,910.96DR 540,430.65DR
= EQUIVALENT TOTAL =
TOTAL EQUITY 3494895.47
**US$ US EXCHANGES** *US$

--
rgds


"Roger Govier" wrote:

Hi

The problem is the wrapping created by the NG reader you are using
where
the line has been wrapped and is incomplete.
The line should be continuous as below

Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)

--
Regards

Roger Govier


"spaceage" wrote in message
...
hi,

when i copied this code in vb and tried executing the macro, it
is
showing
syntax error at this line:
Set cell = Worksheets("Sheet2").UsedRange.Find(.Cells(i,

can u please help.
i can build the macros thru excel but do not have much knowledge
on
vb
code.

--
rgds


"Bob Phillips" wrote:

Public Sub ProcessData()
Const TEST_COLUMN As String = "A" '<=== change to suit
Dim i As Long
Dim iLastRow As Long
Dim cell As Range
Dim sh As Worksheet
Dim sFirst As String

With Worksheets("Sheet1")

iLastRow = .Cells(.Rows.Count,
TEST_COLUMN).End(xlUp).Row
For i = 1 To iLastRow

Set cell = Nothing
Set cell =
Worksheets("Sheet2").UsedRange.Find(.Cells(i,
"A").Value)
If Not cell Is Nothing Then sFirst = cell.Address
Do
If Not cell Is Nothing Then
cell.Value = .Cells(i, "B").Value
Set cell =
Worksheets("Sheet2").UsedRange.FindNext(cell)
End If
Loop While Not cell Is Nothing

Next i

End With

End Sub


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail
in
my
addy)

"spaceage" wrote in message
...
Sheet 1 contains following data
find Replace with
krw usd
jpy usd
aud cad
gbp euro

Sheet 2 contains whole range of data with words to find
located
at
different
rows/coulums.
Is there any formula/vb code wherein it can find above words
and
replace
with mentioned words against it.

rgds


--
rgds