| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Import matching up the Headers name instead of the Column letter
With Workbooks(MyFile).Worksheets("Trade Report") If Trim(.Range("W" & unaplRow)) = "Purchase" Then 'Or Trim(.Range("R" & unaplRow)) = "961011" Then ThisWorkbook.Worksheets("TransactionsBuys").Activa te ThisWorkbook.Worksheets("TransactionsBuys").Range( "F" & MastRow) = C_Code(.Range("X" & unaplRow)) 'ThisWorkbook.Worksheets("TransactionsBuys").Range ("F" & mastRow) = "20955" 'ExecBroker ThisWorkbook.Worksheets("TransactionsBuys").Range( "A" & MastRow) = StrRegion 'Region ThisWorkbook.Worksheets("TransactionsBuys").Range( "B" & MastRow) = Trim(.Range("J" & unaplRow)) 'Fund ThisWorkbook.Worksheets("TransactionsBuys").Range( "C" & MastRow) = "=RIGHT(RC[-1],3)" ' Abbr fund ThisWorkbook.Worksheets("TransactionsBuys").Range( "D" & MastRow) = Trim(.Range("L" & unaplRow)) 'Cusip or AssetID 'Column E is Blank becuase there is no AssetCode ThisWorkbook.Worksheets("TransactionsBuys").Range( "G" & MastRow) = Format(Trim(.Range("R" & unaplRow)), "yymmdd") 'trade Date ThisWorkbook.Worksheets("TransactionsBuys").Range( "H" & MastRow) = Format(Trim(.Range("S" & unaplRow)), "yymmdd") ThisWorkbook.Worksheets("TransactionsBuys").Range( "I" & MastRow) = Today ThisWorkbook.Worksheets("TransactionsBuys").Range( "J" & MastRow) = StrSettleLoc ThisWorkbook.Worksheets("TransactionsBuys").Range( "K" & MastRow) = Format(Trim(.Range("P" & unaplRow)), "00.00") ThisWorkbook.Worksheets("TransactionsBuys").Range( "U" & MastRow) = StrInterest ThisWorkbook.Worksheets("TransactionsBuys").Range( "V" & MastRow) = Format(Trim(.Range("C" & unaplRow)), "00.00") ThisWorkbook.Worksheets("TransactionsBuys").Range( "AC" & MastRow) = StrPAOnly ThisWorkbook.Worksheets("TransactionsBuys").Range( "AU" & MastRow) = StrIssueClass MastRow = MastRow + 1 End If End With |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Import matching up the Headers name instead of the Column letter
Yes, if header is in row 1 and is called ABC then with ThisWorkbook.Worksheets("TransactionsBuys") set c = .rows(1).find(what:="ABC", lookin:=xlvalues, lookat:=xlwhole) if c is nothing then msgbox("Could not find header : " & "ABC") else .Cells(MastRow, c.column) = C_Code(.Range("X" & unaplRow)) end if end with "Crazyhorse" wrote: With Workbooks(MyFile).Worksheets("Trade Report") If Trim(.Range("W" & unaplRow)) = "Purchase" Then 'Or Trim(.Range("R" & unaplRow)) = "961011" Then ThisWorkbook.Worksheets("TransactionsBuys").Activa te ThisWorkbook.Worksheets("TransactionsBuys").Range( "F" & MastRow) = C_Code(.Range("X" & unaplRow)) 'ThisWorkbook.Worksheets("TransactionsBuys").Range ("F" & mastRow) = "20955" 'ExecBroker ThisWorkbook.Worksheets("TransactionsBuys").Range( "A" & MastRow) = StrRegion 'Region ThisWorkbook.Worksheets("TransactionsBuys").Range( "B" & MastRow) = Trim(.Range("J" & unaplRow)) 'Fund ThisWorkbook.Worksheets("TransactionsBuys").Range( "C" & MastRow) = "=RIGHT(RC[-1],3)" ' Abbr fund ThisWorkbook.Worksheets("TransactionsBuys").Range( "D" & MastRow) = Trim(.Range("L" & unaplRow)) 'Cusip or AssetID 'Column E is Blank becuase there is no AssetCode ThisWorkbook.Worksheets("TransactionsBuys").Range( "G" & MastRow) = Format(Trim(.Range("R" & unaplRow)), "yymmdd") 'trade Date ThisWorkbook.Worksheets("TransactionsBuys").Range( "H" & MastRow) = Format(Trim(.Range("S" & unaplRow)), "yymmdd") ThisWorkbook.Worksheets("TransactionsBuys").Range( "I" & MastRow) = Today ThisWorkbook.Worksheets("TransactionsBuys").Range( "J" & MastRow) = StrSettleLoc ThisWorkbook.Worksheets("TransactionsBuys").Range( "K" & MastRow) = Format(Trim(.Range("P" & unaplRow)), "00.00") ThisWorkbook.Worksheets("TransactionsBuys").Range( "U" & MastRow) = StrInterest ThisWorkbook.Worksheets("TransactionsBuys").Range( "V" & MastRow) = Format(Trim(.Range("C" & unaplRow)), "00.00") ThisWorkbook.Worksheets("TransactionsBuys").Range( "AC" & MastRow) = StrPAOnly ThisWorkbook.Worksheets("TransactionsBuys").Range( "AU" & MastRow) = StrIssueClass MastRow = MastRow + 1 End If End With |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I change column headers from a number to a letter | Excel Discussion (Misc queries) | |||
| how do I change column headers from a number to a letter | Excel Discussion (Misc queries) | |||
| wise ass who changed column headers to numbers instead of letter | Excel Discussion (Misc queries) | |||
| How to replace column letter in refferences with a function using the old column letter? | Links and Linking in Excel | |||
| How to replace column letter in refferences with a function using the old column letter? | Excel Worksheet Functions | |||
Linear Mode
