how to update dynamic range via vba in Excel 2010

[legethi]

Hi all,

I'm looking on how to change update a dynamic range from sheet A cell 7, with the name of a new sheet that I create with vb. The name of the sheet is store as public in "param".
My dynamic range is as follow:
='SAL Gateway'!$B$23+'SAL Policy Server'!$B$23+PIM!$B$23+SIPCAN!$B$23+MYSYSLOG!$B$23+'XP windows 7'!$B$23+'Efficient SDS250'!$B$23+Sniffer!$B$23+CMTB!$B$23+'HTTP SFTP'!$B$23+SMOKEPING!$B$23+'System Manager'!$B$23
If I create a new sheet I would like to add it at the and of this dynamic range as per below example:

='SAL Gateway'!$B$23+'SAL Policy Server'!$B$23+PIM!$B$23+SIPCAN!$B$23+MYSYSLOG!$B$23+'XP windows 7'!$B$23+'Efficient SDS250'!$B$23+Sniffer!$B$23+CMTB!$B$23+'HTTP SFTP'!$B$23+SMOKEPING!$B$23+'System Manager'!$B$23+'newsheet'!$B23

Thanks in advance for the help.

Telecom Administrator

 

[SkipVought]

Hi,

What do you mean by "dynamic range"?

What is cell 7? I ASSUMED A7

[tt]
Sheets("A").Cells(7, 1).Formula = "='SAL Gateway'!$B$23+'SAL Policy Server'!$B$23+PIM!$B$23+SIPCAN!$B$23+MYSYSLOG!$B$23+'XP windows 7'!$B$23+'Efficient SDS250'!$B$23+Sniffer!$B$23+CMTB!$B$23+'HTTP SFTP'!$B$23+SMOKEPING!$B$23+'System Manager'!$B$23+'" & [highlight #FCE94F]param[/highlight] & "'!$B23"
[/tt]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[legethi]

Hi SkipVought,

Thanks for your fast answer. I just realized that I have forgot 1 detail. What I need is first to read the actual dynamic range and then update the actual range with the new sheet. I can translate what I'm looking to do as follow:

Actual dynamic range = actual dynamic range + '" & param & "'!$B23"
Hope that I'm clear on what I try to explain but in case if there is some understanding point on what I need, let me know it.

Telecom Administrator

 

[SkipVought]

You did not answer my question, "What do you mean by 'dynamic range'"?

What you have is a formula!

Hi,

What do you mean by "dynamic range"?

What you have is:
A value from SAL Gateway PLUS
a value from SAL Policy Server PLUS
a value from PIM PLUS
a value from SIPCAN PLUS
A value from MYSYSLOG PLUS
a value from 'XP MYSYSLOG 7 PLUS
a value from Efficient SDS250' PLUS
A value from Sniffer PLUS
A value from CMTB PLUS
A value from HTTP SFTP PLUS
A value from SMOKEPING PLUS
a value from System Manager

And then add a value from the new sheet.

Oh, yes, they all happen to be values in B23 on each sheet, but it is not defined as a range but as a sum of descrete values.

And it is a very odd workbook structure.

What makes B23 so significant?

Finally, what is "Actual dynamic range"?




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[legethi]

Ok so first why B23 is so significant it's just because this is the cell where the disk capacity size needed for a Virtual Machine is referenced. So as you can see I have already a certain number of Virtual Machine referenced but it's can be possible that we have to add a new virtual machine in the list due to vendor solution change, troubleshooting tools change, etc ... so for this, based on the Virtual machine created, I start from the first which is "SAL Gateway" and from the Name manager in Formula ribbon I have created a range call VM_HD.

I have done this based on some finding in google related on how to create dynamics range. the only difference is instead of using cell from same column or row inside the same worksheet, I use 1 cell per Virtual machine sheet.

In my vba windows I have done a test about my VM_HD where I use the following code:
Worksheets("Physical Server capacity").Range("L8").Formula = "=SUM(VM_HD)"
which give me the total for each B23 cell coming from each worksheet created for a Virtual Machine.
Now my goal is to add in this range called VM_HD the reference of a new sheet that I can have to create from VBA. in fact the Sheet creation with form user to set all necessary information that I need is already working and I just miss to manage the auto update of my total capacity for all Virtual Machine taking new virtual machine creation information from b23 cell.



On another sheet that I call "Physical Server Capacity" I make

Telecom Administrator

 

[SkipVought]

I use 1 cell per Virtual machine sheet.

So does that mean that you have a separate sheet for each virtual machine?

If so, you need to do some reading about proper database structure. This data/table can all be on one sheet if it is properly structured and it will make the analysis of the data orders of magnitude simpler!

Tables/Sheets ought never to be made for data values.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[legethi]

I'm looking for doing a database / table, I just need to have an Excel file which is use then after to perform ordering of the physical server, then provide necessary information to different entity to perform the virtual Machine installation.
At the start this excel file was in a way of manual add but I'm looking to simplify the usage to avoid any issue in the different formula that I have.
I know how to create a new sheet in which 1 I can add name of all my worksheet in a single column by using the following code:
Set NewSheet = Sheets.Add(Type:=xlWorkSheet)
For i = 5 To Sheets.Count
NewSheet.Cells(i, 1).Value = Sheets(i).Name
Next i
but where I'm block is how to read the formula and then update the formula with the new name of the worksheet + the cell where my info is located, which give me "+ 'Worksheet name'!B23", to be put at the end of the dynamic range or if other way end of the formula.

I have try also the following approach in my code based on the formula itself:
Dim Sht As Object
Dim arSheetNames() As String
Dim i As Integer

i = 5
For Each Sht In ActiveWindow.SelectedSheets
i = i + 1
ReDim Preserve arSheetNames(1 To i)
arSheetNames(i) = Sht.Name
Next Sht

startSheet = arSheetNames(LBound(arSheetNames))
endSheet = arSheetNames(UBound(arSheetNames))

Worksheets("Physical Server capacity").Range("J11").Formula = "=SUM(" & startSheet & "+" & endSheet & "!B23)"

but this give me the following result for the formula: =sum+"param!b23"

Telecom Administrator

 

[SkipVought]

Worksheets("Physical Server capacity").Range("J11").Formula = "=SUM(" & startSheet & [highlight #8AE234]":"[/highlight] & endSheet & "!B23)"

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]