Populate workbook dynamically??

[Guest]

Please forgive me if this has been done before..

Im looking to populate a workbook dynamically from information input into
another workbook in the same worksheet.

Im looking to do capacity planning for racks in our data centres. I want to
get excel to create graphical representations of each rack using information
I will put into a workbook. Is this at all possible?

Each graphical rep will be 42 cells high to represent the U hieght of the
rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start
point (of say 26 for this KVM). I want to include other information as well
but if this is not possible and a no starter then I will look elsewhere...

Can you help? Have I made any sense?

 

[Guest]

This works for me

I entered data in to a table like this to feed the macro

Name start height
kvm 3 4
ups 8 10
IBM 20 45



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/30/2006 by xxxxxxxx
'

'
Dim srow 'data start point
Dim slength 'data height
Dim count 'data entry row
Dim sname 'value to appear in graphic

Dim current 'worksheet name
current = ActiveSheet.name
count = 2

While count < 100


Cells(count, 2).Select 'column 2 contains starting number
srow = ActiveCell.Value
Cells(count, 3).Select 'column 3 contains height
slength = ActiveCell.Value
Cells(count, 1).Select 'column 1 contains name to put in graphic
sname = Selection.Formula

Sheets("Sheet1").Select
If (slength > 0 And srow > 0) Then
Range(Cells(srow, 1), Cells(srow + slength, 1)).Select
Selection.Formula = sname 'change this to a color change, or border to make
more graphical
End If
count = count + 1
Sheets([current]).Select
Wend

End Sub

 

[Guest]

Thanks so much for this. Ive read through it and it makes some sense to me
although I am not very good with VB. I do however, get the following error
on "more graphical" line:

Compile Error: Sub or Function not defined.

Am I doing something wrong?

This works for me

I entered data in to a table like this to feed the macro

Name start height
kvm 3 4
ups 8 10
IBM 20 45



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/30/2006 by xxxxxxxx
'

'
Dim srow 'data start point
Dim slength 'data height
Dim count 'data entry row
Dim sname 'value to appear in graphic

Dim current 'worksheet name
current = ActiveSheet.name
count = 2

While count < 100


Cells(count, 2).Select 'column 2 contains starting number
srow = ActiveCell.Value
Cells(count, 3).Select 'column 3 contains height
slength = ActiveCell.Value
Cells(count, 1).Select 'column 1 contains name to put in graphic
sname = Selection.Formula

Sheets("Sheet1").Select
If (slength > 0 And srow > 0) Then
Range(Cells(srow, 1), Cells(srow + slength, 1)).Select
Selection.Formula = sname 'change this to a color change, or border to make
more graphical
End If
count = count + 1
Sheets([current]).Select
Wend

End Sub

Please forgive me if this has been done before..

Im looking to populate a workbook dynamically from information input into
another workbook in the same worksheet.

Im looking to do capacity planning for racks in our data centres. I want to
get excel to create graphical representations of each rack using information
I will put into a workbook. Is this at all possible?

Each graphical rep will be 42 cells high to represent the U hieght of the
rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start
point (of say 26 for this KVM). I want to include other information as well
but if this is not possible and a no starter then I will look elsewhere...

Can you help? Have I made any sense?

 

[Andrew Taylor]

The "more graphical" line actually belongs at the end of
the comment on the previous line - the line has been
wrapped at some point in its journey though Usenet. So
you can just delete it. (Or move it back to where it
belongs.)

Thanks so much for this. Ive read through it and it makes some sense to me
although I am not very good with VB. I do however, get the following error
on "more graphical" line:

Compile Error: Sub or Function not defined.

Am I doing something wrong?

This works for me

I entered data in to a table like this to feed the macro

Name start height
kvm 3 4
ups 8 10
IBM 20 45



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/30/2006 by xxxxxxxx
'

'
Dim srow 'data start point
Dim slength 'data height
Dim count 'data entry row
Dim sname 'value to appear in graphic

Dim current 'worksheet name
current = ActiveSheet.name
count = 2

While count < 100


Cells(count, 2).Select 'column 2 contains starting number
srow = ActiveCell.Value
Cells(count, 3).Select 'column 3 contains height
slength = ActiveCell.Value
Cells(count, 1).Select 'column 1 contains name to put in graphic
sname = Selection.Formula

Sheets("Sheet1").Select
If (slength > 0 And srow > 0) Then
Range(Cells(srow, 1), Cells(srow + slength, 1)).Select
Selection.Formula = sname 'change this to a color change, or border to make
more graphical
End If
count = count + 1
Sheets([current]).Select
Wend

End Sub

Please forgive me if this has been done before..

Im looking to populate a workbook dynamically from information input into
another workbook in the same worksheet.

Im looking to do capacity planning for racks in our data centres. I want to
get excel to create graphical representations of each rack using information
I will put into a workbook. Is this at all possible?

Each graphical rep will be 42 cells high to represent the U hieght of the
rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start
point (of say 26 for this KVM). I want to include other information as well
but if this is not possible and a no starter then I will look elsewhere...

Can you help? Have I made any sense?

 

[Guest]

OK, thanks for that. I am still having trouble running the macro. Im
gettiing a runtime error '13' type mismatch on:

Range(Cells(srow, 1), Cells(srow + slength, 1)).Select

Should I be altering this macro to fit my data set? What should I be doing
to get this working correctly on the data you provided me with fugazi48?

The "more graphical" line actually belongs at the end of
the comment on the previous line - the line has been
wrapped at some point in its journey though Usenet. So
you can just delete it. (Or move it back to where it
belongs.)

Thanks so much for this. Ive read through it and it makes some sense to me
although I am not very good with VB. I do however, get the following error
on "more graphical" line:

Compile Error: Sub or Function not defined.

Am I doing something wrong?

This works for me

I entered data in to a table like this to feed the macro

Name start height
kvm 3 4
ups 8 10
IBM 20 45



Sub Macro2()
'
' Macro2 Macro
' Macro recorded 8/30/2006 by xxxxxxxx
'

'
Dim srow 'data start point
Dim slength 'data height
Dim count 'data entry row
Dim sname 'value to appear in graphic

Dim current 'worksheet name
current = ActiveSheet.name
count = 2

While count < 100


Cells(count, 2).Select 'column 2 contains starting number
srow = ActiveCell.Value
Cells(count, 3).Select 'column 3 contains height
slength = ActiveCell.Value
Cells(count, 1).Select 'column 1 contains name to put in graphic
sname = Selection.Formula

Sheets("Sheet1").Select
If (slength > 0 And srow > 0) Then
Range(Cells(srow, 1), Cells(srow + slength, 1)).Select
Selection.Formula = sname 'change this to a color change, or border to make
more graphical
End If
count = count + 1
Sheets([current]).Select
Wend

End Sub

:

Please forgive me if this has been done before..

Im looking to populate a workbook dynamically from information input into
another workbook in the same worksheet.

Im looking to do capacity planning for racks in our data centres. I want to
get excel to create graphical representations of each rack using information
I will put into a workbook. Is this at all possible?

Each graphical rep will be 42 cells high to represent the U hieght of the
rack, each bit of kit will have a U hieght (of say 2 for a KVM) and a U start
point (of say 26 for this KVM). I want to include other information as well
but if this is not possible and a no starter then I will look elsewhere...

Can you help? Have I made any sense?