Solved: Variable Named Ranges

[khalid79m]

:banghead:
Sub ColumnSetting()

'Settings
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row



'File_Name

With Range("A2:Z2")
.Find(What:="File_Name").Activate
End With
ActiveCell.Offset(1, 0).Activate
With ActiveCell & lastrow ??
.Name = ?? '
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "@" 'This will make the range a text property
End With

End Sub

Hi guys I am trying to great named ranges, as my end users have a tendancy to move columns around and want it to be flexiable, i am trying to write a macro which will look in range a2 to z2 and find file_name, once this has been found I want to go down 1 cell , then using this cell and the lastrow , name it using the cell name above.

for example after the search say for instance m2 was File_Name, so the macro would pick m3 & lastrow and name it with m2,

and if the next it was run file_name had been moved to a2 it would pick a3 & lastrow and name it with a2

Can U help

Edit Lucas: vba tags added

[lucas]

When posting, select your code and hit the vba button, not the quote button to format your code as above.

Double posting will just cause you problems........as well as the admins.

Urgent in your title will cause people to shy away from your thread.

Other than that you are doing just fine.

I don't really understand your problem though. You are wishing to create named ranges based on find? and lock it so noone can edit it when it is protected.

The problem I see is that you don't have to select areas to lock for protection, just the opposite actually, you select and unlock area's you don't want protected.......all other cells will be locked.

[khalid79m]

Thanks pressed the wrong button

I am wishing to create a named range based on find


Sub ColumnSetting()

'Settings
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row



'File_Name

With Range("A2:Z2")
.Find(What:="File_Name").Activate
End With
ActiveCell.Offset(1, 0).Activate
With ActiveCell & lastrow ??
.Name = ?? '
End With

End Sub


how do i select activcell & lastrow ???
and name it with found cell ??

[mikerickson]

You could do it with VBA:
With ThisWorkbook.Sheets("Sheet1").Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Name = "File_Name"

End With
End With
End With


Or, if there are no blank rows below "File_Name", it could be done completely with Name definitions.

Name: fnCol
RefersTo: =OFFSET(Sheet1!$A:$A,0,MATCH("File_Name",Sheet1!$A$2:$Z$2,0)-1)

Name: File_Name
RefersTo: =OFFSET(fnCol,2,0,COUNTA(fnCol)-COUNTA(OFFSET(fnCol,0,0,2,1)),1)

[khalid79m]

Could you break the code down so that I can follow it stage by stage, this is way advance for me , also one question what is happening at this stage After:=.Range("A1"),

[khalid79m]

With ThisWorkbook.Sheets("CallData","Submit").Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Name = "File_Name"
.Locked = True
.NumberFormat = "@"

End With
End With
End With

[mikerickson]

With ThisWorkbook.Sheets("Sheet1").Range("A2:Z2")this is the range to search for "File_Name"

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
this returns the cell in that range the contains "File_Name". .Range("A1") (with the dot) starts the search at the first cell of the outer With.

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))
this specifies a range that begins one cell below that found cell, .Offset(1,0),
and goes until the last cell in the column , .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp).
The .Parent of the found cell is the worksheet.

.Name = "File_Name" this names that range.

[khalid79m]

Sub Macro4()

'FILENAME
With ThisWorkbook.Sheets("CallData").Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Name = "File_Name"
.Locked = True
.NumberFormat = "@"

End With
End With
End With

'SITE
With ThisWorkbook.Sheets("CallData").Range("A2:Z2")

With .Find(What:="Site", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Name = "Site"
.Locked = True
.NumberFormat = "@"

End With
End With
End With
'Department
With ThisWorkbook.Sheets("CallData").Range("A2:Z2")

With .Find(What:="Department", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Name = "Department"
.Locked = True
.FormulaR1C1 = "=File_Name*Site"
.NumberFormat = "0"

End With
End With
End With
End Sub

some columns such as site or department may be empty until the user fills them in, but column a will still have a file name for them, for instance column is a3:a165 filled with file numbers, and b3:b100 have site filled in , that means b101:b165 are missed of the named range, but i need them included... if that makes sense hence why i dimmed the lastrow as "A"

[mikerickson]

To do multiple sheets, you could pass the sheet as an argument to a sub.
Sub test()
Call SetFile_NameOnSheet(ThisWorkbook.Sheets("CallData"))
Call SetFile_NameOnSheet(ThisWorkbook.Sheets("Submit"))
End Sub

Sub SetFile_NameOnSheet(mySheet As Worksheet)
With mySheet.Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp))

.Parent.Names.Add Name:="File_Name", RefersToR1C1:="=" & .Address(, , xlR1C1, True)
.Locked = True
.NumberFormat = "@"

End With
End With
End With
One thing to be aware of is that having the same name (File_Name) refer to different ranges on different sheets requires activating the proper sheet for Range("File_Name") to refer to the appropriate name.

[mikerickson]

Have you tried the Name definitions that I posted in #4? They react dynamicaly as the user enters data onto the sheet.

If you want VB to set the named ranges dynamicaly, Worksheet_Change events will be needed to keep up with the users data entry.

[khalid79m]

I have to sheets, one is CallData and the other Submitted, both are identical and have the saved named ranges. The inputter fills in the CallData sheet and once they are happy they press submit button which then copys the completed data from calldata to submitted.

What I want to is name all the columns by first of all finding the column header between a2 and z2 and then selecting the cell below and the lastrow based on column A and naming it with the found value and repeat this until i have found all my columns.

I want this to apply to both sheets...

this is what I had before blocks of code and i just changed the sheet name
Sub A()
'Settings
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

'COLUMN A FILE_NAME
With Range("A3:A" & lastrow)
.Name = Range("A2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "@" 'This will make the range a text property
End With
'COLUMN B SITE
With Range("B3:B" & lastrow)
.Name = Range("B2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "@" 'This will make the range a text property
End With

'COLUMN C DEPARTMENT
With Range("C3:C" & lastrow)
.Name = Range("C2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "@" 'This will make the range a text property
End With

'COLUMN D MONTH
With Range("D3:D" & lastrow)
.Name = Range("D2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "0" 'This will make the range a text property
End With

'COLUMN E YEAR
With Range("E3:E" & lastrow)
.Name = Range("E2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "0" 'This will make the range a text property
End With

'COLUMN F STAFF_NAME
With Range("F3:F" & lastrow)
.Name = Range("F2").Value 'The selected range will be given a name based on the value in A2
.Locked = True 'This will set the range property as locked, so when a password is active nobody can edit it
.NumberFormat = "@" 'This will make the range a text property
End With


I really appreciate your help and training ,, is there anything u could suggest as this blocks and blocks of code is no good to maintian

[khalid79m]

Column A would always dictate the lastrow, as without a filename they wouldnt input information.

[mikerickson]

When working with different sheets, its best to qualify range references.
The syntax Range("A:A") refers to the active sheet, while ThisWorkbook.Sheets("CallData").Range("A:A") is on worksheet CallData.

If the headers on CallData and Submit are identical and there is a named "aName" that refers to a range of CallData. And if aName is scoped as a workbook-wide name rather than a sheet-wide name. This will copy the data from aName to the same cell addresses on Submit.


With ThisWorkbook
With Range("aName")
.Sheets("Submit").Range(.Address).Value = .Value
End With
End With

[khalid79m]

Sub Macro1()
'Settings
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

With Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0) & lastrow)

.Name = "File_Name"

End With
End With
End With

End Sub


Can this be done ?? with range(.offset(1,0)&lastrow) ???

[mikerickson]

I'd use Resize to set the number of rows in the named range.
Sub Macro1()
'Settings
Dim lastrow As Long
lastrow = Cells(Rows.Count, "A").End(xlUp).Row

With Range("A2:Z2")

With .Find(What:="File_Name", After:=.Range("A1"), LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)

With Range(.Offset(1, 0)).Resize(lastRow-2, 1)

.Name = "File_Name"

End With
End With
End With

End SubThinking about the overall goal. It sounds like you have a sheet CallData, where the user inputs data and sometimes re-arranges the columns and you want the appropriate data from the (possibly re-arranged) columns put in the matching column of Summary (that has its columns in a fixed order.)
If that is the extend of your use of the Named ranges, they aren't nessesary.

You could just copy CallData's data onto Summary and then sort (horizontaly) by a custom list of the headers in proper order.

[khalid79m]

With Range(.Offset(1, 0)).Resize(lastrow - 2, 1)

onthis line comes up with run time error'91'
object variable or with block variable not set???

I have attached a sample file also if this helps.

[khalid79m]

I am only using the call data sheet, I wont need the submitted sheet anymore .

[mikerickson]

The routine looks for "File_Name" in row 2. The contents of C2 are "filename", which does not match. Which caused the error. As did my mangling the sytnax of that Range statement.

You might try this to set the names from exiting row 2 values rather than searching for what is supposed to be there.

Sub Macro1()
'Settings
Dim lastRow As Long
Dim oneCell As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each oneCell In ThisWorkbook.Sheets("CallData").Range("A2:Z2")
With oneCell
If CStr(.Value) <> vbNullString Then
.Offset(1, 0).Resize(lastRow - 2, 1).Name = CStr(.Value)
End If
End With
Next oneCell

End Sub

[khalid79m]

:clap: :clap: :clap: :clap: :clap: :clap: :clap: :clap: U ARE TRULY A SUPERSTAR

Thankyou

[mikerickson]

I'm not sure that this is the best technique.

The names of the named ranges aren't constant and depend on the values the user puts in A2:Z2.

How is one going to write spreadsheet formulas or VB statements if the name of the range changes?

INDIRECT could be used, but if you are going to use these names for spreadsheet formulas, you might see if Labels will do the same for you.

Alternatly, one could put a list validation on A2:Z2 to force the user to put one of a limited list of values. e.g. force the user to enter "File_Name" rather than "filename". That would force the name to be from that list and formulas would be easier to write.

[khalid79m]

The headers will be subject to validation, it works a treat so far, if I get stuck ill come back to you. ( they some times upto some extra columns in with there own headers so mine get moved along alittle apart from column a so this way I can calculate my formulas easily rather than restricting them to a specific cell address.)


I am experiencing one problem to enhance the visual display some cells between a2:z2 are blank ie carry no header, how can I get the above script to only work if the cell is not empty

[khalid79m]

Sub Macro1()
'Settings
Dim lastRow As Long
Dim oneCell As Range
lastRow = Cells(Rows.Count, "A").End(xlUp).Row

For Each oneCell In ThisWorkbook.Sheets("CallData").Range("A2:Z2")
With oneCell
If CStr(.Value) <> vbNullString Then
.Offset(1, 0).Resize(lastRow - 2, 1).Name = CStr(.Value)
End If
End With
Next oneCell

End Sub

This is linked to column a and lastrow but i need the opposite now on another sheet, i need it not be lastrow based on column A , i need the lastrow based on the column which its looking at ... does that make sense?

[mikerickson]

Sub Macro1()
'Settings

Dim oneCell As Range


For Each oneCell In ThisWorkbook.Sheets("CallData").Range("A2:Z2")
With oneCell
If CStr(.Value) <> vbNullString Then
Range(.Offset(1,0), .EntireColumn.Cells(.Parent.Rows.Count, 1).End(xlUp)).Name = CStr(.Value)
End If
End With
Next oneCell

End Sub

[khalid79m]

Ok , i see the parent.row means the row it is looking at...

Mickrickson , I think you great we need more people like you here, xld I think your great too, I just wish one day I hold the knowledge you guys have, and inturn pass it on.. keep up the good work... is there anyway to sponsor this site ?

[mikerickson]

The .Parent is inside the With oneCell ... End With.
oneCell.Parent is a Worksheet object.

I don't recall what version you are using, but if it's pre-2007, you can substitute the constant 65536 for the .Parent.Rows.Count.

[lucas]

is there anyway to sponsor this site ?

You can use the pay Pal link at the top of each page of the forum. Right next to your login info at the top right of the page.

[khalid79m]

I see the paypal link, I will dontate