Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 18
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 34
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Referring to Ranges in Change-Event Macro? Wuddus Excel Discussion (Misc queries) 4 August 24th 07 08:12 PM
Referring to the previous selected sheet in a macro michaelberrier Excel Discussion (Misc queries) 2 June 12th 06 01:35 PM
Referring to a hyperlink [email protected] Excel Discussion (Misc queries) 2 March 1st 06 03:11 PM
insert columns macro is putting 2 columns instead of 1 AGH Excel Worksheet Functions 2 February 27th 06 03:36 PM
Referring to a column Hru48 Excel Discussion (Misc queries) 1 January 31st 06 06:59 PM


All times are GMT +1. The time now is 01:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"