Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
Public Sub ProcessData()
Dim i As Long Dim iLastRow As Long Dim iRow As Long Dim iCol As Long Dim sh As Worksheet With ActiveSheet iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row Set sh = Worksheets("Sheet2") sh.Range("A1:G1").Value = Array( _ "Prj no.", "Project name", "Euro", "RP", "SGD", "USD", "Yen") iRow = 1 For i = 2 To iLastRow If .Cells(i, "A").Value < "" Then iRow = iRow + 1 sh.Cells(iRow, "A").Value = .Cells(i, "A").Value sh.Cells(iRow, "B").Value = .Cells(i, "B").Value End If iCol = Application.Match(.Cells(i, "C").Value, sh.Rows(1), 0) sh.Cells(iRow, iCol).Value = .Cells(i, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
Bob, thank your for your quick response. Could you explain some more, on how
you get ("A1:G1 and sh.Cells(iRow, "A").Value = .Cells(i, "A").Value I am not so clear on this since I am an Accountant, but try to do it my self by just selfthought. Thanks very much. Frank "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim iLastRow As Long Dim iRow As Long Dim iCol As Long Dim sh As Worksheet With ActiveSheet iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row Set sh = Worksheets("Sheet2") sh.Range("A1:G1").Value = Array( _ "Prj no.", "Project name", "Euro", "RP", "SGD", "USD", "Yen") iRow = 1 For i = 2 To iLastRow If .Cells(i, "A").Value < "" Then iRow = iRow + 1 sh.Cells(iRow, "A").Value = .Cells(i, "A").Value sh.Cells(iRow, "B").Value = .Cells(i, "B").Value End If iCol = Application.Match(.Cells(i, "C").Value, sh.Rows(1), 0) sh.Cells(iRow, iCol).Value = .Cells(i, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
Frank,
A1:G1 is just the cells in row 1 of the target sheet that I drop the headings into. I use iRow to keep track of the row written to on the second sheet, and i is used to track the row I am on within the source sheet. So .Cells(i, "A").Value is the current value in column A of the source sheet, and I write that to the next free row on sheet 2. -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... Bob, thank your for your quick response. Could you explain some more, on how you get ("A1:G1 and sh.Cells(iRow, "A").Value = .Cells(i, "A").Value I am not so clear on this since I am an Accountant, but try to do it my self by just selfthought. Thanks very much. Frank "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim iLastRow As Long Dim iRow As Long Dim iCol As Long Dim sh As Worksheet With ActiveSheet iLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row Set sh = Worksheets("Sheet2") sh.Range("A1:G1").Value = Array( _ "Prj no.", "Project name", "Euro", "RP", "SGD", "USD", "Yen") iRow = 1 For i = 2 To iLastRow If .Cells(i, "A").Value < "" Then iRow = iRow + 1 sh.Cells(iRow, "A").Value = .Cells(i, "A").Value sh.Cells(iRow, "B").Value = .Cells(i, "B").Value End If iCol = Application.Match(.Cells(i, "C").Value, sh.Rows(1), 0) sh.Cells(iRow, iCol).Value = .Cells(i, "D").Value Next i End With End Sub -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Frank Situmorang" wrote in message ... 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
Thanks Joel, I will start understanding all this codes first. But anyway, are
all thes codes to be combined as one macro?, and I will assingn it to a Autoshape button?. Where shall I write these code is it in sheet 1 or sheet 2. Thanks in advance. 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conversion from columnwise to rowwise
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula for rowwise | Excel Worksheet Functions | |||
how do I end a worksheet columnwise. | Excel Worksheet Functions | |||
Array formula that works columnwise? | Excel Worksheet Functions | |||
rowwise query | Excel Worksheet Functions | |||
rowwise total! | Excel Worksheet Functions |