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