Using a range in a cell for vba code

[oblix]

Hi there all
I make significant changes to sheets and cells
These cells during changes can also be protected or unprotect during changes
I have many macro to clear ranges and perform other functions
The problem is it that it can be time consuming to manually find and change/update the ranges.

Is it possible to create an admin sheet and in column A give description of range and place range in column B eg "A1:A5,C5,B10:B40" as text
then use that ranges pasted in column B to be used as a range in vba code?
Then I only have to keep updating the admin sheet.

In short anything that is in (" ") in vba code range read from the cell

Or another method
Any suggestions

 

[offthelip]

try this, Note I have populated the ranges with their name, which is obviusly not quite what you want!!

Sub test()
 Dim rnam As String
  lastrow = Cells(Rows.Count, "B").End(xlUp).Row
  inarr = Range(Cells(1, 1), Cells(lastrow, 2))
 For i = 2 To lastrow
 rnam = inarr(i, 2)
 Range(rnam) = inarr(i, 1)
 Next i
End Sub

 

[oblix]

Thank you for quick reply

I dont think this will work for my needs but it has put me in another direction (or maybe it will and I just dont understand code well enough)
If I am correct all my macros will then need substancial changes for this to work.
Is there then a way to create a sub to name all the ranges I use and just refer to that range name in other macros
or
Can a class be created to solve this much easier (consider I know absolute ly notjing about classes)

 

[offthelip]

Yes you certainly can add named ranges like this:

Sub test()
 Dim rnam As String
  lastrow = Cells(Rows.Count, "B").End(xlUp).Row
  inarr = Range(Cells(1, 1), Cells(lastrow, 2))
 For i = 2 To lastrow
 rnam = inarr(i, 2)
 ActiveWorkbook.Names.Add Name:=inarr(i, 1), RefersTo:=ActiveWorkbook.Worksheets("Sheet1").Range(rnam)
 Next i
End Sub

 

[oblix]

Thank you for reply
Sorry for my ignorance
So if I have sheet1 that looks like this:
"image sheet 1"

and one of my macros is this:

Sub Clear_Unit_Info()
 Call UnProtectActiveSheet
    Worksheets("UNIT INFO").Range("E5:E26, E37:E40, E63:E64, G5:G6, G8:G9, G11, G13:G14, G17:G18, G26").ClearContents
    Range("C1").Select
    Call ProtectActiveSheet
End Sub

I want the range "............" that is currently in macro to rather auto place in macro with what is in cell C4 on sheet 1
How do I use your macro ? or change my macro


like if you give a named range and use in formulas many times and then I only change data in named range once and all formulas still work with changes made
Thats what I am trying to achive for my vba cose too
Sorry for not explaining this well enought

 

[Tupe77]

offthelip already answered this, but his code was so much more sophisticated that I put this as a template.

Sub RangeFromCellValue()

Dim RNG1 As Range
    Set RNG1 = Range(Sheets("AdminSheet").Range("B2").Value) 'The value of cell B2 of the "AdminSheet" -sheet is changed to a range.
    MsgBox RNG1.Address ' Prints the address of the range formed from the cell value. Just for testing

End Sub

It is good to note that:
Cell B2 does not contain information on which sheet the area belongs to, so the default is
the sheet that is active at the time the code is executed.
The name of the sheet can be given in the adjacent cell, in VBA or maybe it is not even needed.

Basically the answer to your first question is:
Range(address.value)
Where:
address = cell where text is
value to read cell contents
Range() transform text to range

No quotes in the cell because .value is text and VBA adds quotes automatically.

 

[offthelip]

the way I would do it to to rewrite all your macro using named ranges and then just you can define what the range is in your admin sheet. your code would need to be modifed like this:

Sub test()
 Dim rnam As String
  lastrow = Cells(Rows.Count, "B").End(xlUp).Row
  inarr = Range(Cells(1, 1), Cells(lastrow, 2))
 For i = 2 To lastrow
 rnam = inarr(i, 2)
 ActiveWorkbook.Names.Add Name:=inarr(i, 1), RefersTo:=ActiveWorkbook.Worksheets("UNIT INFO").Range(rnam)
 Next i
End Sub

Sub Clear_Unit_Info()
rangelist = Array("range1", "range2", "range3", "range4", "range5", "range6", "range7", "range8", "range9")  ' edit ths array to suit your needs
' Call UnProtectActiveSheet
   For i = 0 To UBound(rangelist)
    Range(rangelist(i)).ClearContents
   Next i
    Range("C1").Select
 '   Call ProtectActiveSheet
End Sub

I had this as my "admin " sheet although I didn't specifically address the admin sheet in the test subroutine, which it should do :
Note you don't have to use the same names or the same list for each of your macros, you could have entirely seperate lists.

 

[oblix]

Wow thanks for all the solutions
I am sooo out off my depth here , but I am going to give it a try
I will play with these and see how it works
then I will come back and give feedback on my results
I am not going to mark this as resolved yet
thanks once again for feedback offthelip and Tupe77