Vba Question: How To Rename Sheets With A Keyword In Them?

Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select

 
Sub DeleteNode()
If MsgBox("Delete This Node?", vbYesNo, "Discard Changes") = vbYes Then
GoTo Delete
Else
MsgBox ("Deletion Cancelled")
Exit Sub
End If
Delete:
Application.ScreenUpdating = False
Call Unhide
If MsgBox("Are you sure?", vbYesNo, "Discard Changes") = vbYes Then
Sheets("Node " & Sheets("Data_Entry").Range("Nodenumber")).Select
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
Sheets("Calcs").Range("CurrentNodes").Value = Sheets("Calcs").Range("CurrentNodes").Value - 1
Sheets("Data_Entry").Range("Nodenumber").Value = Sheets("Data_Entry").Range("Nodenumber").Value - 1
Sheets("Data_Entry").Select
Sheets("Data_Entry").Range("Date").Value = ""
Sheets("Data_Entry").Range("Node_Description").Value = ""
Sheets("Data_Entry").Range("Node_Design_Intention").Value = ""
Sheets("Data_Entry").Range("Drawings_Used").Value = ""
On Error GoTo Skip
Sheets("Data_Entry").Range("_CopyTable").Select
Selection.Delete
Skip:
Sheets("Data_Entry").Range("SelectNode").Value = Sheets("Data_Entry").Range("Nodenumber").Value
Call SelectNode
Call hide
Application.ScreenUpdating = True
MsgBox ("Node Deleted")
Exit Sub
Else
MsgBox ("Deletion Cancelled")
End If
End Sub

for i = 1 to n
dim node&i as BNode ' this is a Netica specific object
end for

I need a row number to automatically increment as I copy a formula horizontally across columns. Sample spreadsheet downloadable he

http://www.youshare.com/Guest/18365825fec3723c.xls.html

Or viewable as a Google Doc he

http://spreadsheets.google.com/ccc?k...SMFdHdkE&hl=en

To elaborate, the formula in question starts in Column T. In T2, the formula compares Node 1 to itself - hence the zero. In T2, it compares Node 1 to Node 2, in T3, Node 1 to Node 3, and so on. Column U does the same thing, except it compares Node 2 to Node 1, Node 2 to Node 2, Node 2 to Node 3, and so on. I want to copy out my formula so that all 50 nodes in the matrix are filled in, in this manner.

The formula increments properly when copied vertically. Take a look at the representative portion of the formula right after the ABS in cell T2: ($C$2-$C2). The formula stays "anchored" on Node 1 via the $C$2. The second part, $C2, increments as you copy it down, to $C3, $C4 and so on. (The same is true for the rest of the formula - it just goes ($D $2-$D2), ($E$2-$E2) and so on.)

Now take a look at Column U. The "anchor" changes from $C$2 to $C$3. That's because Node 2 is now the basis for comparison. In Column V, the anchor changes to $C$4, because Node 3 is the basis for comparison.

The thing is, the anchors in Columns U and V were updated by hand. That is to say, if you copy the formula in Column T one cell to the right, nothing increments. I realize that this is "proper" Excel behavior - when you copy formulas horizontally, generally only the column will increment. But I would really love a way to force the row number of the anchor to automatically increment as I copy the formula to the right. (Note that the anchor has to retain the $ before the row number because when I copy the formula down, that portion of the formula must stay fixed.)

Please let me know if you need any further clarifications. And if you have any suggestions, I would be very grateful. Thank you.

Hello,

I am trying to create a macros which will tell me which nodes I have to join together with a line in a irregular mesh (Lines need to join nodes vertically and horizontally). For example here is a typical mesh:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152

So for example (for horizontal lines) ,

-ex. node 1 is joined to node 2
-ex. Node 7 os joined to node 8

The output I desire would be in two colums, with the starting node in one column and the corresponding node in the next column.

-ex 1 2
2 3
3 4
etc

I have written a macros, but it doesnt work, and I can't figure out why, see below,

Sub Nodes()
J = 0
For x = 1 To ((Worksheets("Sheet1").Cells(2, 1).Value) - 1)

If Worksheets("Sheet1").Cells(22, x) "" Then

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)

Else

Do

x = x + 1

Loop Until (Worksheets("Sheet1").Cells(22, x).Value) = ((Worksheets("Sheet1").Cells((J + 39), 1).Value) + 1)

Worksheets("Sheet1").Cells((J + 39), 1).Value = Worksheets("Sheet1").Cells(22, x)

End If

J = J + 1

Next x
End Sub


Thanks in advance for any help!!

NAVARC.

I want to set the parent nodes to the equivalent of CheckBox1.Value = Null if >0 and < All Child nodes are selected.

The syntax for changing the value of individual nodes seems to be NodeObject.Checked = True but I cannot find a way to set a third (greyed out) state.
Here is some of the code:

Code:

 
Private Sub FlagKeyWordsTreeView_NodeCheck(ByVal Node As MSComctlLib.Node)
Dim CKIDS As Integer
Dim I As Integer
Dim AI As Integer
Dim BI As Integer
Dim X As Integer
 
    CKIDS = 0
    If Node.Children = 0 Then
    '    If Node.Checked = False Then Node.Parent.Checked = False
        I = Node.Parent.Children
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
            If FlagKeyWordsTreeView.Nodes(X).Checked = True Then
                CKIDS = CKIDS + 1
                If CKIDS = I Then
                    Node.Parent.Checked = True
                ElseIf CKIDS > 1 Then
                    Node.Parent.Checked = False         ' want to put NULL here
                 Else
                    Node.Parent.Checked = False
                End If
 
            End If
        Next X
    Else
        Set A = FlagKeyWordsTreeView.Nodes(Node.Index + 1).FirstSibling
        Set B = FlagKeyWordsTreeView.Nodes(Node.Index + 1).LastSibling
        AI = A.Index
        BI = B.Index
        For X = AI To BI
           FlagKeyWordsTreeView.Nodes(X).Checked = Node.Checked
        Next X
    End If
End Sub

Hi All,

i was wonderig if anyone knows how to count the number of parents of a treeview node?

I 'm trying to work out what "Level" each node is at i.e. if there are 3 parents to a node it is at level 4 and so on?

Hope that's clear guys

any help is greatly appreciated and i'll defo add to your rep

Cheers

I have a spreadsheet with about 10 org charts on it. I created these via Insert=>Diagram=>Organizational Chart.

On each sheet I also have a list of positions. I would now like to create some VBA code to enable me to do the following.

When clicking on a cell in the list VBA inserts a node just below the master node with the cell content being linked to the inserted node.
Unfortunately I cannot even seem to get VBA to insert the node let alone fill the text in.

Can anybody out there help at all?

Hi all, Im reviewing some macros and user forms within a spreadsheet, and attempting to work out how the original author has created some functionality

A number of items are defined as nodes. i.e. "Dim strWhatever As node"

When attempting to extract the forms to another sheet, and run code to set them up, I get told "User-defined type not defined", which I assume means that "node" is defined somewhere within the existing VBA, but I can't find it in the existing file

Some code that is falling over is as follows:

Private Sub NodeCheck(ByVal node As MSComctlLib.node)
Call doCheckChildren(node)
Call doCheckParent(node)
End Sub

When checking, the very first line is highlighted

Does anyone have any idea what this means?!

Hello, I hv been encountering this problem whenever i assigned the variable "Node" where the system prompted "User-defined object not defined".

Do I need to manually create the Node object in the object library? or Can point me out how to solve this. Thanks in advance.

Hi,

I have a worksheet and I have named some ranges for some cells in the in, worksheet. (I did it by right click on the cell and choose Name a Range.... and gave a name for the cell.)

Now i have a xml which contains nodes with the same name as the name of the range. Now I want to populate values in the sheets cell from the xml for each range that is named sheet .

For eg:

Code:

<book>
<bookid>100</bookid>
<bookname>abcd</bookname>
</book>

Cells in the worksheet have a namerange as bookid and bookname.
Now for those cells i want to store 100, abcd respectively.

I can for loop each node in the xml , but i dont want to hardcode bookid, bookname in the for each loop for the node name and also range name, eg

Code:

Set list = xmlDoc.DocumentElement.SelectNodes("Booklist")
For Each node In list
 Source.Worksheets("Books").Range("bookid").Value = node.SelectSingleNode("bookid").Text
Next

How can i achieve this??

Lets Just say that the work book name is...

CSM B-12 Node 1.xls

I Have one set of code doing the following..

Code:

Dim txt As String
txt = ThisWorkbook.Name
WorkbookName = Left(txt, Len(txt) - 4)
Sheets("Summary").Range("AL2") = WorkbookName 'column AL

Which returns CSM B-12..perfect...

Then i have a a formula that does...

Code:

=IF(COUNTIF(AL2,"*B-*"),SUBSTITUTE(AL2,"B-",""),"")

Which returns

CSM 12

also perfect...semi...

What i would like to do is bring this into the said macro to return in...

In AL2 = CSM B-12 (Removing Node 1 from the Name of the workbook Name)
In AL3 = CSM 12 (removing B- and Node 1)
In AL4 = 1 (Removing everything but keeping the 1 from the end)

Id like to get the above to work in the macro vs. formulas...Ideas??

Thank you

Hi,

Is there a way to get innerText from all childnodes of a node quickly? I am using VBA.

A web page has a node "H1" which has several childnodes, some of them are "p" and some are "STRONG". I need to get the text in these nodes. I tried to use getelementsbytagname and getattribute methods, but it is getting too complex. Is there an easy way?

Thanks,

MG.

Hi All,

I have a code which helps me to downloads only the files with specific extensions like xls, xlsx, ppt etc from outlook, Earlier it use to download all attachments but still I have following issues which are still outstanding and I need help for fixing this issues.

1) Download the files from multiple subfolders .i.e. select the folder in tree view and use that selection in main macro to download attachments.
2) Download only the latest files.

I have selected the true for checkboxes in the property window of treeview1 so that we can select the multiple folders.

I also got the below code to get the selected folders of outlook as selection for downloading the attachments from them. I need help to incorporate this so that we can fix the issue no.1

VB:

Private Sub TreeView1_NodeCheck(ByVal Node As MSComctlLib.Node) 
    Dim n As Node 
     
    If Node.Parent Is Nothing Then 
        Set n = Node.Child 
        Do Until n Is Nothing 
            n.Checked = Node.Checked 
            Set n = n.Next 
        Loop 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I am attaching my updated macro file for your reference. please have a look.

Thanks a lot for your help in advance.

Hi all,

I have a computer program here which chucks out thousands of lines of data in a report text file, and I need to extract some data from it.

If I copy and paste the relevant bit, I get lots of lines similar to this one (where the text is in one single cell in each row):

Code:

   128     node  dist = 0 elev = -597 temp = 13.7

Basically what I would like to do, is write a macro that can scan through all of the lines in a loop, and if the line contains the phrase "node dist =" then I would like it to extract 3 numbers and deposit them in three separate columns the numbers are "Node Distance", "Elevation", and "Temperature", which in this case are "0", "-597" and "13.7".

If the line contains the phrase "node dist", then the format of the line will always be the same (the number at the beginning (128) is just the line number). So I guess the macro would need to look for each number (including any - signs) after an "=". Is this possible? If so, how on earth do I go about trying to do it??

Thanks in advance!

Nick

Hello I am new to this forum so please bear with me...

I am looking for help in developing steps in a spreadsheet to calculate a sum based on a few conditions. The basis of my spreadsheet works fine, however I am trying to add the contents of a cell to another by checking other cells.

For example:

Cells A1:A10 contain a numerical sequence (1-10 respectively) to indicate a "Node"

Cells B1:B10 contain a user selected number (1-10) which defines the Node it is fed from. We'll call this "Fed From Node".......ie. If Cell B2=1, that means that Node 2 is fed from Node 1.

Cells C1:C10 contain a value which is input by the user. We'll call this "Amps"

Cells D1:D10 would show the total "Amps" of the current cell added to all others that are fed from same.

What I am ultimately trying to figure out is how to add the "Amps" to each "Node" and show the results in column D.

More specifically if the spreadshet looks like the following:

Node-----Fed From Node-----Amps-------Total
1--------------0----------------------0--------------22.5
2--------------1---------------------10--------------22.5
3--------------0----------------------0---------------0
4--------------2---------------------7.5-------------12.5
5--------------4----------------------5---------------5

How would I add the "Amps" from Node5 to Node4 and Node4 to Node2 to achieve a total of 22.5 IF Node5 is fed from Node4 and Node4 is fed from Node2?

I have tried macros and different excel formulas for the past few days and I am really stumped.

Any help would be greatly appreciated.

Hi,

I need to Count no. of cells having common text string at start and then varying later. The data is like this in a single column and may run into hundreds of rows,

Splice at node: 13637605_X86 Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 4.850000 Total C.S.A. = 4.850000 C.S.A. = 0.000000 Right Hand Side
Page: 2
Splice at node: 13653204_X16 Part Number: BLUNT_CUT_ASSY
Splice at node: 13746734_X16 Part Number: BLUNT_CUT_ASSY
Page: 3
Splice at node: 19153416_A96 Part Number: BLUNT_CUT_ASSY
Splice at node: 1_13235988_A Part Number: BLUNT_CUT_ASSY
Page: 4
Splice at node: 1_13637605_X Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 3.500000 Total C.S.A. = 3.500000 C.S.A. = 0.000000 Right Hand Side
Splice at node: 1_19153416_A Part Number: BLUNT_CUT_ASSY
Page: 5
Splice at node: 2_13637605_X Part Number: 7209-5299-0W
Left Hand Side C.S.A. = 5.000000 Total C.S.A. = 5.000000 C.S.A. = 0.000000 Right Hand Side

Splice at node: 13637605_X86 Part Number: 7209-5299-0W


I need a formula is to count the cells which start with text string "Splice at node:",

Thanks in advance,

awagdarikar

Hi,
I'm trying to write to a worksheet and I'm not sure how to make the code position the active cell one cell above the "NODE"(defined name), insert a new row, and then write the data from the userform to the appropriate columns in that new row.
So far, I've got this - it works but always writes the userform values one cell BELOW the "NODE"!!!

Code:

 
 RowCount = Worksheets("Sheet1").Range("NODE").CurrentRegion.Rows.Count
     
          With Worksheets("Sheet1").Range("NODE")
               .Offset(RowCount, 0).Value = Me.txtFirstName.Value
               .Offset(RowCount, 1).Value = Me.txtLastName.Value
               .Offset(RowCount, 2).Value = Me.cboDepartment.Value
               .Offset(RowCount, 3).Value = DateValue(Me.txtDate.Value)
               .Offset(RowCount, 4).Value = Me.txtAmount.Value
               .Offset(RowCount, 5).Value = Me.txtDescription.Value 
          End With 

Any suggestions on how to select the "NODE", go up one row in the same column, and then write the data?

Thank you!

Gino

I'm at a loss figuring out how to set the color of a specific node within a
shape created from BuildFreeform. It's been converted to a shape. I'm looking
up the index of a value in a range, and want to set the color of the
corresponding node in the shape. Any help is much appreciated.
thx,
j

Hello Guys...I need help here

I have a textfile and i'm using vba for excel to read the textline and paste in into excel..
I have a textfile that contain a thousand of data
the purpose of this project is to copy the textline from the starting text that i choose until it reach the end that i also choose..

here, i have attached a sample textfile that i need to copy to the excel...

fyi, A73, Z1 and A74 F+ is the node number..
For example i want to read A73 until node Z1 only, and my first node is A73, and the last node is Z1
I want it to read the all the textline until it reach the GRT1P1+U8+W8 line of Z1...
My problem is how do i do that...

FYI also, i have thousands of node number..This is just a sample..

Regards...

Hello Guys...I need help here

I have a textfile and i'm using vba for excel to read the textline and paste in into excel..
I have a textfile that contain a thousand of data
the purpose of this project is to copy the textline from the starting text that i choose until it reach the end that i also choose..

here, i have attached a sample textfile that i need to copy to the excel...Sample.txt

fyi, A73, Z1 and A74 F+ is the node number..
For example i want to read A73 until node Z1 only, and my first node is A73, and the last node is Z1
I want it to read the all the textline until it reach the GRT1P1+U8+W8 line of Z1...
My problem is how do i do that...

FYI also, i have thousands of node number..This is just a sample..

Regards...

I'm looking to build a matrix from three columns of data. The three columns essentially have an a-node value, b-node value, and the result value.

I want to build a matrix where I can input the a-node values as row headers, the b-node values as column headers, and the result value will be populated in the corresponding cell (see attached). I've tried a combination of index and match functions but can't seem to get anything to work.

Eventually, I would like to build a series of matrices from a long set of three columns of data.

If you have any questions regarding the above, please post any follow up questions. Any help would be greatly appreciated!

Dear All,

I need to form a matrix in Excel based on user inputs satisfying below Conditions: Pls help me in writing a macro or necessary worksheet functions to develop the same.. Mail to reachspk@gmail.com .. Thnx

Userinputs:
S.No. -- leftNode# --- RightNode#
1 ------- 1 -------- 5
2------- 2------- 4
3------- 3 ------- 4
4------- 4------- 5
5------- 5------- 6
6------- 5------- 6
7------- 6------- 7
8------- 4------- 8
Algorithm:
1. Check the left node of element 1, if that node is appearing for first time (in user input area), then populate 1 in corresponding Q1 location
2. Similarly check for other elements and populate 1, if above condition is satisfied
3. If left node of any element is connected to any previous or following element, populate 1 on corresponding element locations, if that node no. appears on right (in user input table), and -1, if it comes on left
4. If any of the left node no. is repeated more than once for elements, then populate 1 on corresponding location of first element
5. In the remaining locations, it should populate zero

Resultant Matrix should be:

8x8 matrix

1 0 0 0 0 0 0 0
0 1 0 0 0 0 0 0
0 0 1 0 0 0 0 0
0 1 1 0 0 0 0 -1
0 0 0 0 1 0 0 0
1 0 0 1 -1 0 0 0
0 0 0 0 1 1 0 0
0 1 1 -1 0 0 0 0

Thanks in advance,
Prakash