Please help to solve my problem

C

cnu nagula

Board Regular
Joined
Aug 28, 2008
Messages
83
Dear friends,

Please help to solve below problem.

i have sheet with different headings with particular certificates (row wise certificates), i want to choose heading and relevant certificates are to be displayed

here with example:

a1 -- a
a2 -- b
a3 -- c
a4 --
a5 -- aa
a6 -- ab
a7 -- ac
a8 --
a9 -- ab
a10 -- ac
a11 --
a12 -- aaa
a13 -- aab

like this i have entered some date.

if i entered number in d1 as 1 all the data d2..d4 the cell contains a1 to a3 data to be display like if i entred 2 the data in a5 to a7 is to be displayed in d2 to d4.

please help in this regards
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
Do you know how to add VBA to a workbook? Is a VBA solution acceptable?
 
Upvote 0
If so, paste this into the code module for the worksheet:-
Code: 
[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1]  Dim iLastRow As Long
  Dim iRow As Long
  Dim iGroup
  Dim iNew As Long
  
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Address(0, 0) <> "B1" Then Exit Sub
  
  Range("B2:B" & CStr(Rows.Count)).ClearContents
  
  If Not IsNumeric(Range("B1")) Then Range("B2") = "Error: must be a number!"
  If Range("B1").Value = 0 Then Range("B2") = "Error: must not be zero!"
  If Range("B1").Value < 0 Then Range("B2") = "Error: must be a positive number!"
  If Range("B1").Value <> Int(Range("B1").Value) Then Range("B2") = "Error: must be a whole number!"
  
  If Not IsEmpty(Range("B2")) Then Exit Sub
  
  iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
  iGroup = 1
  
  If Range("B1").Value = 1 Then
    iGroup = 1
    iRow = 0
  Else
    For iRow = 2 To iLastRow
      If Not IsEmpty(Cells(iRow - 1, 1)) And IsEmpty(Cells(iRow, 1)) Then
        iGroup = iGroup + 1
        If iGroup = Range("B1") Then Exit For
      End If
    Next iRow
  End If
  
  If iGroup = Cells(1, 2) Then
    iNew = 1
    Do Until IsEmpty(Cells(iRow + 1, 1))
      iRow = iRow + 1
      iNew = iNew + 1
      Cells(iNew, 2) = Cells(iRow, 1)
    Loop
  Else
    Range("B2") = "Error: only " & CStr(iGroup) & " groups found!"
  End If
    
End Sub[/SIZE][/FONT]
 
Upvote 0
Thank you, working in vb.

how to change the cell address, to actually in my work sheet.

my data is in N28:N120

the input vaue is in O28

and the data is to be displayed at P28:p

please help,

thanking you once again
 
Upvote 0
Try this:-
Code: 
[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim iLastRow As Long
  Dim iRow As Long
  Dim iGroup
  Dim iNew As Long
  
  If Target.Cells.Count > 1 Then Exit Sub
  If Target.Address(0, 0) <> "O28" Then Exit Sub
  
  Range("P28:P120").ClearContents
  Range("O29").ClearContents
  
  If Not IsNumeric(Range("O28")) Then Range("O29") = "Error: must be a number!"
  If Range("O28").Value = 0 Then Range("O29") = "Error: must not be zero!"
  If Range("O28").Value < 0 Then Range("O29") = "Error: must be a positive number!"
  If Range("O28").Value <> Int(Range("O28").Value) Then Range("O29") = "Error: must be a whole number!"
  
  If Not IsEmpty(Range("O29")) Then Exit Sub
  
  iLastRow = 120
  iGroup = 1
  
  If Range("O28").Value = 1 Then
    iGroup = 1
    iRow = 27
  Else
    For iRow = 28 To iLastRow
      If Not IsEmpty(Cells(iRow - 1, 14)) And IsEmpty(Cells(iRow, 14)) Then
        iGroup = iGroup + 1
        If iGroup = Range("O28") Then Exit For
      End If
    Next iRow
  End If
  
  If iGroup = Range("O28").Value Then
    iNew = 27
    Do Until IsEmpty(Cells(iRow + 1, 14))
      iRow = iRow + 1
      iNew = iNew + 1
      Cells(iNew, 16) = Cells(iRow, 14)
    Loop
  Else
    Range("O29") = "Error: only " & CStr(iGroup) & " groups found!"
  End If
    
End Sub[/SIZE][/FONT]
I think I caught all the changes but please shout if it doesn't work properly.
 
Upvote 0
Actually this is better as it puts all the variables up at the top, so if the requirements change, there's only one place to modify the code:-
Code: 
[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Worksheet_Change(ByVal Target As Range)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1] Const iFirstRow As Long = 28[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Const iLastRow As Long = 120[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Const sDataSource As String = "N"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Const sDataTarget As String = "P"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Const sEntryColumn As String = "O"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Dim iRow As Long[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Dim iGroup As Long[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Dim iNew As Long[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Target.Cells.Count > 1 Then Exit Sub[/SIZE][/FONT]
 
[FONT=Courier New][SIZE=1] If Target.Address(0, 0) <> sEntryColumn & CStr(iFirstRow) Then Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Range(sDataTarget & CStr(iFirstRow) & ":" & sDataTarget & CStr(iLastRow)).ClearContents[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Range(sEntryColumn & CStr(iFirstRow + 1)).ClearContents[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Not IsNumeric(Range(sEntryColumn & CStr(iFirstRow))) Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a number!"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Range(sEntryColumn & CStr(iFirstRow)).Value = 0 Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must not be zero!"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Range(sEntryColumn & CStr(iFirstRow)).Value < 0 Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a positive number!"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Range(sEntryColumn & CStr(iFirstRow)).Value <> Int(Range(sEntryColumn & CStr(iFirstRow)).Value) Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a whole number!"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] iGroup = 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If Range(sEntryColumn & CStr(iFirstRow)).Value = 1 Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   iGroup = 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   iRow = iFirstRow - 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Else[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   For iRow = iFirstRow To iLastRow[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     If Not IsEmpty(Cells(iRow - 1, sDataSource)) And IsEmpty(Cells(iRow, sDataSource)) Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]       iGroup = iGroup + 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]       If iGroup = Range(sEntryColumn & CStr(iFirstRow)) Then Exit For[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Next iRow[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1] If iGroup = Range(sEntryColumn & CStr(iFirstRow)).Value Then[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   iNew = iFirstRow - 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Do Until IsEmpty(Cells(iRow + 1, sDataSource))[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     iRow = iRow + 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     iNew = iNew + 1[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]     Cells(iNew, sDataTarget) = Cells(iRow, sDataSource)[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Loop[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] Else[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]   Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: only " & CStr(iGroup) & " groups found!"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] End If[/SIZE][/FONT]
[FONT=Courier New][SIZE=1] [/SIZE][/FONT]
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]
 
Last edited:
Upvote 0
Hi,

Will this formula approach suffice:
<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>N</th><th>O</th><th>P</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">28</td><td style=";">$N$28</td><td style="text-align: right;background-color: #FFFF00;;">2</td><td style=";">$N$32</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style=";">$N$29</td><td style="text-align: right;;"></td><td style=";">$N$33</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style=";">$N$30</td><td style="text-align: right;;"></td><td style=";">$N$34</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style=";">$N$32</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style=";">$N$33</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style=";">$N$34</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style=";">$N$36</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style=";">$N$37</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style=";">$N$38</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P28</th><td style="text-align:left">=OFFSET(<font color="Blue">$N$27,(<font color="Red">(<font color="Green">O$28-1</font>)*4</font>)+ROW(<font color="Red"></font>)-27,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P29</th><td style="text-align:left">=OFFSET(<font color="Blue">$N$27,(<font color="Red">(<font color="Green">O$28-1</font>)*4</font>)+ROW(<font color="Red"></font>)-27,0</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">P30</th><td style="text-align:left">=OFFSET(<font color="Blue">$N$27,(<font color="Red">(<font color="Green">O$28-1</font>)*4</font>)+ROW(<font color="Red"></font>)-27,0</font>)</td></tr></tbody></table></td></tr></table><br />
 
Upvote 0
ok it is working.

i have another problem with, the input number in cell O29 is linked with drop down tool, the data is not changed when i used with drop down method, the data is only changed when i used manual entering no in O29, please help, the number is changed with linked to another cell

thanking you
 
Upvote 0
O29 or O28?

Is O29 free to show an error message?

Perhaps you should paste range M25:Q35 into a message so we can see what we have around the ranges we're playing with?

(Please put borders around the cells and select a small font so it displays neatly.)
 
Last edited:
Upvote 0
If you have a drop-down in, say, A1, and you change its value, it doesn't trigger the Worksheet_Change event as you say.

However if you put =A1 in another cell, say A2, then when you change A1, A2 is recalculated and this triggers the Worksheet_Calculate event.

You can use that instead.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Solved
SUBSTITUTE function
Replies
3
Views
298
Fluff
Fluff
S
  • Locked
  • Question Question
A Different way to Calculate Ratios using GDC & or MIN ?
Replies
1
Views
260
Fluff
Fluff
S
  • Solved
A Different way to Calculate Ratios using GDC & or MIN ?
2
Replies
12
Views
585
StrawberryDreams
S
gtd526
  • Question Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
364
gtd526
gtd526
gtd526
  • Question Question
vba Sorting Columns w/ 2 Criteria's
Replies
1
Views
331
gtd526
gtd526

Forum statistics

Threads
1,224,564
Messages
6,179,544
Members
452,925
Latest member
duyvmex

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top