Referring to columns from within a macro

[Blobbies]

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

 

[MrDave]

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("address",$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

 

[MrDave]

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),sheetone!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]

Thanks "MrDave" that gives me heaps to go on with! Cheers!

 

[MrDave]

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("address",$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..