Please help to solve my problem

[cnu nagula]

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

 

[cnu nagula]

Mr Ruddles,

Input value in O28 only.

Worksheet_Calculate is working.

when ever the value in o28 updated, the method is to work.

Thankyou.

Mr.al b cnu. Thanks for our suggesion, but it is not working correctly as Mr.Ruddles method.

 

[Ruddles]

You can't trigger Worksheet_Change directly by changing a drop-down. I need a spare cell to put =O28 in and another to track changes. Where can I put them?

 

[cnu nagula]

you may put it on O5

 

[Ruddles]

I did ask for two cells - I really do need them both. Okay, I've set them to O5 and O6 but you can change them to whatever you like:-

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Private Sub Worksheet_Calculate()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Const iFirstRow As Long = 28
  Const iLastRow As Long = 120
  Const sDataSource As String = "N"
  Const sDataTarget As String = "P"
  Const sEntryColumn As String = "O"
  Const sCalcCell As String = "[COLOR=red][B]O5[/B][/COLOR]"
  Const sChangeCell As String = "[COLOR=red][B]O6[/B][/COLOR]"
  
  Dim iRow As Long
  Dim iGroup As Long
  Dim iNew As Long
  
  If Range(sEntryColumn & CStr(iFirstRow)) = Range(sChangeCell) Then Exit Sub
  Range(sChangeCell) = Range(sEntryColumn & CStr(iFirstRow))
  
  Range(sDataTarget & CStr(iFirstRow) & ":" & sDataTarget & CStr(iLastRow)).ClearContents
  Range(sEntryColumn & CStr(iFirstRow + 1)).ClearContents
  
  If Not IsNumeric(Range(sEntryColumn & CStr(iFirstRow))) Then
    Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a number!"
    Exit Sub
  End If
  
  If Range(sEntryColumn & CStr(iFirstRow)).Value = 0 Then
    Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must not be zero!"
    Exit Sub
  End If
    
  If Range(sEntryColumn & CStr(iFirstRow)).Value < 0 Then
    Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a positive number!"
    Exit Sub
  End If
    
  If Range(sEntryColumn & CStr(iFirstRow)).Value <> Int(Range(sEntryColumn & CStr(iFirstRow)).Value) Then
    Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: must be a whole number!"
    Exit Sub
  End If
    
  iGroup = 1
  
  If Range(sEntryColumn & CStr(iFirstRow)).Value = 1 Then
    iGroup = 1
    iRow = iFirstRow - 1
  Else
    For iRow = iFirstRow To iLastRow
      If Not IsEmpty(Cells(iRow - 1, sDataSource)) And IsEmpty(Cells(iRow, sDataSource)) Then
        iGroup = iGroup + 1
        If iGroup = Range(sEntryColumn & CStr(iFirstRow)) Then Exit For
      End If
    Next iRow
  End If
  
  If iGroup = Range(sEntryColumn & CStr(iFirstRow)).Value Then
    iNew = iFirstRow - 1
    Do Until IsEmpty(Cells(iRow + 1, sDataSource))
      iRow = iRow + 1
      iNew = iNew + 1
      Cells(iNew, sDataTarget) = Cells(iRow, sDataSource)
    Loop
  Else
    Range(sEntryColumn & CStr(iFirstRow + 1)) = "Error: only " & CStr(iGroup) & " groups found!"
  End If
    
End Sub[/SIZE][/FONT]

 

[cnu nagula]

Error: must not be zero!

the above error is displaying:

in cell o6 is disappearing.

what is 05 and 06 i am not understanding previously it is O28 cell for input number i am totally confused.

if you give your mail id i will attach my excel file

thanking you.

 

[Ruddles]

Sorry, I should have made it clearer: in O5 you have to enter =O28 (O5 is the calculated cell which will trigger the macro).

O6 is where the previous value of O28 is stored: the macro uses that to detect when the value changes.

 

[cnu nagula]

ok i understood, its working.

Thanks lot,

you had spent much time for me, its realy, i thank you very very much......