Joel,

Maybe I need to give you full description of my excel imported report. Since
this a summary of multi grouping in access database, so it is not excactly in
a line for the amount in currency so the layout is like this to be exactly
shown with line number
A B C D
1 Prj no. Project name Currency Invoice Amount
2 xx02 ABC project
3 RP. 2,500
4 USD. 100
5 2,600

There are 2 things which may be taken into consideration in the VBA:
1. Line 2 or project number and name is not in one line with Rp. Currencies
2. There is a subtotal for each project which is actually not needed (see
2600), because it is the total of mixed currencty, but since it was orginated
by the access program itself, so it carries the amount. We do not need this
to be taken in the rowwise report ( or sheet 2 as you said)

Does you suggested code will work even the the format is like that?

Thanks in addvance for your help.

Frank



"Joel" wrote:

This code should work. It will copy data from sheet1 and place it in sheet
2. Enter in sheet 2 the header row from your posting. Put all the
currencies in this header row, not just the ones from your example. the code
searches this header row to find the correct column to place the amounts.

Add to sheet 2 row 1
Prj no. Project name Euro Rp. SGD USD Yen


Sub FixCurrencies()

'Get Range of currencies on Sheet2
With Sheets("Sheet2")
LastColumn = .Cells(1, Columns.Count). _
End(xlToLeft).Column
Set CurrRange = .Range(.Cells(1, "C"), _
.Cells(1, LastColumn))
End With

With Sheets("Sheet1")
'get last row
LastRow = 0
For ColumnCount = 1 To 7
Last = .Cells(Rows.Count, ColumnCount). _
End(xlUp).Row
If Last LastRow Then LastRow = Last
Next ColumnCount

'Get each row of original sheet
Sh2RowCount = 1
For Sh1RowCount = 1 To LastRow
If Not IsEmpty(.Cells(Sh1RowCount, "A")) Then
Sh2RowCount = Sh2RowCount + 1
'copy project info to new worksheet
Sheets("Sheet2").Cells(Sh2RowCount, "A") = _
.Cells(Sh1RowCount, "A")
Sheets("Sheet2").Cells(Sh2RowCount, "B") = _
.Cells(Sh1RowCount, "B")
StartCol = 3
Else
StartCol = 1
End If

'find next column with data
Do While StartCol <= Columns.Count
If Not IsEmpty _
(.Cells(Sh1RowCount, StartCol)) Then

Exit Do
End If
StartCol = StartCol + 1
Loop

'skip rows with no data
If StartCol <= Columns.Count Then
Set c = CurrRange.Find _
(what:=.Cells(Sh1RowCount, StartCol), _
LookIn:=xlValues)
If Not c Is Nothing Then
'find next column with data
StartCol = StartCol + 1
Do While IsEmpty _
(.Cells(Sh1RowCount, StartCol)) And _
StartCol <= Columns.Count

StartCol = StartCol + 1
Loop

Sheets("Sheet2"). _
Cells(Sh2RowCount, c.Column) = _
.Cells(Sh1RowCount, StartCol)
Else
MsgBox ("Missing Currnecy : " & _
.Cells(Sh1RowCount, StartCol))

End If
End If
Next Sh1RowCount
End With

End Sub

"Frank Situmorang" wrote:

Hello,

When importing my access report into excel this is the layout:
Summary of Outstanding Items:
Prj no. Project name Currency Invoice Amount
-------- ---------------- ------------------
xx02 ABC project RP. 2,500
USD. 100

XX03 PQR Project EURO 200
RP 10,000
SGD 2000
Yen 2500
Not all project has the currency, some times it has only Rp./local Currency.

I want to have convert it in one row by projects so that I can make formula
to convert it into Rp/our reporting currency more or less as follows:

Prj no. Project name Euro Rp. SGD USD Yen

xx02 ABC project 2500 100

XX03 PQR Project 200 10,000 2000 2500

I appreciate your helping on what formula/VBA/Macro can I have to do it

Thanks a lot

Frank