Solved

Macro help

Posted on 2014-09-12
15
255 Views
Last Modified: 2014-09-12
I have a sheet which has few data in cell. As of now I am doing with a work around. below is a brief
Please open the excel sheet sho you will get an idea.
Parent data  child data            formula to merge
testparent         testchild1                  testparent and childname testchild1
                            testchild2                 testparent and childnames testchild1,testchild2

Is it possible to take only not null values and show on a different cell. Attached the sample sheet.
Appreciate f you can add a button to show the value (without formula) in a specified cell.
I want a button to show only the not null values to the formula and copy the value(not formula) to a specified cell
ParentChild.xlsx
0
Comment
Question by:-MAS
  • 8
  • 7
15 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40318967
We could do that but need to know where your data lies. like where is the input like you have put in Col A Col B row 2,3,4 or more ??? like would you have several parents following each others or only 1 parent and several childs ??
and will results always be on row 11 and downward ?
gowflow
0
 
LVL 25

Author Comment

by:-MAS
ID: 40318978
Only one parent info we will enter in the sheet and copy and paste the value of the cell (which has formula) to our database. It has more fields that I managed only thing is I cannot take child details as only that column has more than one row. Each time we will enter the data and clear the details after copying it to the database. Can you add a button to clear these fields as well ?so we dont need to manually delete the details every time
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40318996
Well too bad already done it so it take multiple families but for sure it will work as well for 1  !!! pls see the attached workbook and I copied the code for your convenience.

Option Explicit

Sub DisplayFamily()
Dim WS As Worksheet
Dim MaxRow As Long, I As Long, ResRow As Long
Dim sPrt As String, sChld As String, sBase As String

Set WS = ActiveSheet
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
ResRow = 15

sPrt = WS.Cells(2, "A")

If sPrt <> "" Then
    '---> Clean Previous Results
    WS.Range("C:C").ClearContents
    
    '---> Process
    For I = 2 To MaxRow
        If WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt Then
            sPrt = WS.Cells(I, "A")
            ResRow = ResRow + 1
        End If
        
        
        sBase = "Parent name is " & sPrt & " and childnames are "
        Do
            If sChld <> "" Then sChld = sChld & ", "
            sChld = sChld & WS.Cells(I, "B")
            WS.Cells(ResRow, "C") = sBase & sChld
            ResRow = ResRow + 1
            I = I + 1
        Loop Until (WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt) Or I > MaxRow
        
        
        sChld = ""
        sBase = ""
        I = I - 1
    Next I
    
End If

MsgBox ("Family displayed successfully.")
End Sub

Sub ClearData()
Dim WS As Worksheet

Set WS = ActiveSheet
WS.Range("A2:C" & WS.Rows.Count).ClearContents

End Sub

Open in new window



Let me know
gowflow
ParentChild.xlsm
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 25

Author Comment

by:-MAS
ID: 40319012
Many thanks for your work.
I want only the last one appear in the sheet.
If the parent has 1 child it show only the value with 1 child (lets say in C15)
If the parent has 2 children it show only the value with 2 children in the same cell (i.e. in C15)
If the parent has 3 children it show only the value with 3 children in the same cell (i.e. in C15)
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40319020
Here it is
gowflow
ParentChild.xlsm
0
 
LVL 25

Author Comment

by:-MAS
ID: 40319032
Many thanks for your work.
I need only one family details. Once entered we will clear the sheet after copying. Then enter the second family detals.
How to remove the second the second family codes (not important)? you can ignore this if you feel difficult
Anyway. Can you explain what are the codes you used and where if you can?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40319046
It takes as many families as you want not only 2 and it is not a code for family1 then an other code for family2 etc... it loops as long as you have data it will look for the parent name then when found it will put in a variable all childs of this parent until if find a new parent then it will print the result then jump 1 line and then go to next parent until end of data. once reach it will display a message Family displayed or whatever.

So basically if you only put 1 family this code will work and display the result if you put 100 it will display them as well !!!

Hope above explanation help.
gowflow
0
 
LVL 25

Author Comment

by:-MAS
ID: 40319078
Appreciate if you tell me the code details. And I need only one parent to be entered. No need more than 1 parent
Set WS = ActiveSheet
MaxRow = WS.Range("B" & WS.Rows.Count).End(xlUp).Row
ResRow = 15

sPrt = WS.Cells(2, "A")

If sPrt <> "" Then
    '---> Clean Previous Results
    WS.Range("C:C").ClearContents
    
    '---> Process
    For I = 2 To MaxRow
        If WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt Then
            sPrt = WS.Cells(I, "A")
            ResRow = ResRow + 1
        End If
        
        
        sBase = "Parent name is " & sPrt & " and childnames are "
        Do
            If sChld <> "" Then sChld = sChld & ", "
            sChld = sChld & WS.Cells(I, "B")
            I = I + 1
        Loop Until (WS.Cells(I, "A") <> "" And WS.Cells(I, "A") <> sPrt) Or I > MaxRow
        
        WS.Cells(ResRow, "C") = sBase & sChld
        ResRow = ResRow + 1
        sChld = ""
        sBase = ""
        I = I - 1
    Next I

Open in new window

0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 500 total points
ID: 40319108
I already told you Enter1 parent and it will do the job !!

Anyway I commented the step in code and I removed the process for more than 1 parent.
gowflow
ParentChild-V01.xlsm
0
 
LVL 25

Author Comment

by:-MAS
ID: 40319127
Mnay thanks Just write the code to copy the value I will add the button
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40319129
Sorry ?
Anything else needed from me ?
gowflow
0
 
LVL 25

Author Closing Comment

by:-MAS
ID: 40319131
Thanks a lot
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40319141
Your welcome.
gowflow
0
 
LVL 25

Author Comment

by:-MAS
ID: 40319148
I need code to copy the value in C15. I will add the button to copy the value from c15
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40319374
Pls post a new question and put a link in here
gowflow
0

Featured Post

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question