Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to columns from within a macro
I have data coming to me from different companies, which is essentially the
same type of data, but their spreadsheets are formatted differently and in different orders etc. I want to create an analysis spreadsheet of my own, and want to tell it to refer to a column within their spreadsheets for the date, a column for the time, a column for the customer number, a column for the customer details and a column for the customer comments. I thought I might be able to do that by creating a macro in the analysis template that gives me the opportunity to identify the appropriate column or the first cell containing the data I want to use in a column of the company's raw data. Is there a way to do this? Or is there some other way I haven't thought of? I'm not clever enough (yet) to work this out!!?? I look forward to hearing your suggestions, or requests for more info'! Mike NZ |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to columns from within a macro
hi, not so expert here, but I put together something that greatly enhances my
sheet. might be some more than asked for, but since was at it. SEE HYPERLINK, and bottom: JUMP macro. by using fixed reference cells in the header (/columns a-z work/ hidden columns) that never move,, lets say free cells B2 thru Z7 I can put many worker cells. In B2 I might have reference to a column that I can reference in a macro, with a cell that will update as I move columns. In your case maybe as workers move columns you may have to using name conventions that reference "Header" names, (you'll have to figure that out). a dynamic reference formula: Columns: =SUBSTITUTE(SUBSTITUTE(CELL("address",$AG3),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$AG3),"$",""),ROW(),"") Cells: (modified by $ sign in front of row number to "fix/ x a row, or modify ROW portion, hint not here) =SUBSTITUTE(SUBSTITUTE(CELL("address",$EC$4),"$"," "),"","") Cells: =SUBSTITUTE(SUBSTITUTE(CELL("address",$CC5),"$","" ),ROW(),"") HYPERLINKS: (for use in a header line within document will go up or down; Z7 cell is work cell for size of screen view) =HYPERLINK("#"&CELL("address",OFFSET($A$550,IF(ROW ($A$550)ROW($A602)-$Z$7/4,$Z$7,-1),0)),"0") Z7: (AX6 is work cell, for number of rows avail / see at top of screen)-22 rows of header.. =$AX$6-ROW($A$22)-2 Option Explicit Private Sub CommandButton1_Click() Dim B1 As String 'test cell B1 = Range("B1") Dim hidb As String hidb = Range("H3") 'Copy-Paste values to different columns If Range(B1).Value = "D" Then If Range(test4).Value = "2" Then '2nd DL, p1-4 Columns(colP1).Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Copy Columns(colP4).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End If 'HIDE - UNHIDE Columns (button stuff) If Range(B1).Value = "B" Then Range(B1).Select Selection.ClearContents Columns(hidb).Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 128 Range("A1").Select End If If Range(B1).Value = "BB" Then Columns(hidb).Select Selection.EntireColumn.Hidden = True ActiveWindow.LargeScroll ToRight:=-1 Range(B1).Select Selection.ClearContents End If End Sub ______________________________________ Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim topID As String 'must add Dim / Range for all items below: DateC2, J3.. topID = Range("D6") Dim J2 As String J2 = Range("J2") Dim J3 As String J3 = Range("GJ") With Target If .Count 1 Then Exit Sub If Target.Row < topID Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'skip some rows 'CAPS: Mulitple Ranges (caps not: Lcase) If Not Intersect(Target, Range(G2 & "," & G3)) Is Nothing Then With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub Application.EnableEvents = False Target = UCase(Target) End With Application.EnableEvents = True End If 'DATES: must dim what cell dateC2 is in as above If Not Intersect(Me.Range(dateC2), .Cells) Is Nothing Then Application.EnableEvents = False With Me.Cells(.Row, dateC3) 'Destination .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'JUMP: to different cols, need to figure out add diff sheets.. If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then With Me.Cells(.Row, J2) .Offset(0, 0).Select End With End If End If End If End If End With |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to columns from within a macro
some more on hyperlinks, if that's what you need:
HYPERLINK to a location within another document ?? not tested need to add book name to path.. ='[AddressBook.xls]Sheet1'!$B$3 =HYPERLINK("#"&CELL("address",C5),C5) =HYPERLINK("#"&CELL("address",sheetone!C5),sheeton e!C5) =HYPERLINK("C:\MY DOCUMENTS\WORD\MyDoc.DOC#bkmk01") TOP OF DOCUMENT UP/ DOWN LINKS: =HYPERLINK("#"&CELL("address",OFFSET($A$550,-1,0)),"0") =HYPERLINK("#"&CELL("address",OFFSET($A$550,$Z$7,0 )),"0") HYPERLINKS: (for use in a header line within document will go up or down; Z7 cell is work cell for size of screen view) =HYPERLINK("#"&CELL("address",OFFSET($A$550,IF(ROW ($A$550)ROW($A602)-$Z$7/4,$Z$7,-1),0)),"0") Z7: (AX6 is work cell, for number of rows avail / see at top of screen)-22 rows of header.. =$AX$6-ROW($A$22)-2 "Blobbies" wrote: I have data coming to me from different companies, which is essentially the same type of data, but their spreadsheets are formatted differently and in different orders etc. I want to create an analysis spreadsheet of my own, and want to tell it to refer to a column within their spreadsheets for the date, a column for the time, a column for the customer number, a column for the customer details and a column for the customer comments. I thought I might be able to do that by creating a macro in the analysis template that gives me the opportunity to identify the appropriate column or the first cell containing the data I want to use in a column of the company's raw data. Is there a way to do this? Or is there some other way I haven't thought of? I'm not clever enough (yet) to work this out!!?? I look forward to hearing your suggestions, or requests for more info'! Mike NZ |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to columns from within a macro
Thanks "MrDave" that gives me heaps to go on with! Cheers!
"MrDave" wrote: hi, not so expert here, but I put together something that greatly enhances my sheet. might be some more than asked for, but since was at it. SEE HYPERLINK, and bottom: JUMP macro. by using fixed reference cells in the header (/columns a-z work/ hidden columns) that never move,, lets say free cells B2 thru Z7 I can put many worker cells. In B2 I might have reference to a column that I can reference in a macro, with a cell that will update as I move columns. In your case maybe as workers move columns you may have to using name conventions that reference "Header" names, (you'll have to figure that out). a dynamic reference formula: Columns: =SUBSTITUTE(SUBSTITUTE(CELL("address",$AG3),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$AG3),"$",""),ROW(),"") Cells: (modified by $ sign in front of row number to "fix/ x a row, or modify ROW portion, hint not here) =SUBSTITUTE(SUBSTITUTE(CELL("address",$EC$4),"$"," "),"","") Cells: =SUBSTITUTE(SUBSTITUTE(CELL("address",$CC5),"$","" ),ROW(),"") HYPERLINKS: (for use in a header line within document will go up or down; Z7 cell is work cell for size of screen view) =HYPERLINK("#"&CELL("address",OFFSET($A$550,IF(ROW ($A$550)ROW($A602)-$Z$7/4,$Z$7,-1),0)),"0") Z7: (AX6 is work cell, for number of rows avail / see at top of screen)-22 rows of header.. =$AX$6-ROW($A$22)-2 Option Explicit Private Sub CommandButton1_Click() Dim B1 As String 'test cell B1 = Range("B1") Dim hidb As String hidb = Range("H3") 'Copy-Paste values to different columns If Range(B1).Value = "D" Then If Range(test4).Value = "2" Then '2nd DL, p1-4 Columns(colP1).Select Selection.Replace What:="0", Replacement:="", LookAt:=xlWhole, _ SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _ ReplaceFormat:=False Selection.Copy Columns(colP4).Select ActiveSheet.PasteSpecial Format:=3, Link:=1, DisplayAsIcon:=False, _ IconFileName:=False End If End If 'HIDE - UNHIDE Columns (button stuff) If Range(B1).Value = "B" Then Range(B1).Select Selection.ClearContents Columns(hidb).Select Selection.EntireColumn.Hidden = False ActiveWindow.ScrollColumn = 128 Range("A1").Select End If If Range(B1).Value = "BB" Then Columns(hidb).Select Selection.EntireColumn.Hidden = True ActiveWindow.LargeScroll ToRight:=-1 Range(B1).Select Selection.ClearContents End If End Sub ______________________________________ Private Sub Worksheet_Change(ByVal Target As Excel.Range) Dim topID As String 'must add Dim / Range for all items below: DateC2, J3.. topID = Range("D6") Dim J2 As String J2 = Range("J2") Dim J3 As String J3 = Range("GJ") With Target If .Count 1 Then Exit Sub If Target.Row < topID Then Exit Sub If Me.Cells(.Row, "A").Value = "." Then Exit Sub 'skip some rows 'CAPS: Mulitple Ranges (caps not: Lcase) If Not Intersect(Target, Range(G2 & "," & G3)) Is Nothing Then With Target If .Cells.Count 1 Then Exit Sub If .HasFormula Then Exit Sub Application.EnableEvents = False Target = UCase(Target) End With Application.EnableEvents = True End If 'DATES: must dim what cell dateC2 is in as above If Not Intersect(Me.Range(dateC2), .Cells) Is Nothing Then Application.EnableEvents = False With Me.Cells(.Row, dateC3) 'Destination .NumberFormat = "dd" .Value = Now End With Application.EnableEvents = True End If 'JUMP: to different cols, need to figure out add diff sheets.. If Not Intersect(Me.Range(J3), .Cells) Is Nothing Then With Me.Cells(.Row, J2) .Offset(0, 0).Select End With End If End If End If End If End With |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Referring to columns from within a macro
thanks, notes:
Columns: (problem: this eq in row 3 must say 3 here for: AG:AG, else will say AG3:AG3) (problem: this cell references columns: AG:AG, as well as: AG3; if you cut / past over cell AG3 to far right: #REF! stuff happens) =SUBSTITUTE(SUBSTITUTE(CELL("address",$AG3),"$","" ),ROW(),"")&":"&SUBSTITUTE(SUBSTITUTE(CELL("addres s",$AG3),"$",""),ROW(),"") NOTE: I use DIM & RANGE reference instead of column / cell references, to have dynamic reference to changing column/ cell locations. you must enter DIM & RANGE statements for each of the "made-up" names you make. eg missing below: colP1, colP4, dateC2, dateC3.. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Referring to Ranges in Change-Event Macro? | Excel Discussion (Misc queries) | |||
Referring to the previous selected sheet in a macro | Excel Discussion (Misc queries) | |||
Referring to a hyperlink | Excel Discussion (Misc queries) | |||
insert columns macro is putting 2 columns instead of 1 | Excel Worksheet Functions | |||
Referring to a column | Excel Discussion (Misc queries) |