Column to headings heading contents to rows

[HansV]

It's exactly as I predicted. You introduced not only subcategories such as green and red apples, but also a new subdivision of age groups. Trying to cope with all this is inefficient and almost impossible to maintain.
This needs to be done in a relational database, not in Excel.

[adam]

As you have predicted Its a difficult task to write complicated codes in excel. for that reason I have planned to modify my workbook. Thanks for the help & suggestion Hans.

I really do appreciate that.

[adam]

How should I modify the code in the "sales" sheet if I want to add the text “Thank You” to the row after the last row where data appears in my worksheet?

Say for example, if the last row that contains data in the “sales” sheet is row “B10” I want the text “Thank You” to appear in the row “B12”, when I write the accession number in cell B2

[HansV]

Try something like this:

Code: Select all

    Dim m As Long
    With Worksheets("Sales")
        m = .Cells(.Rows.Count, 2).End(xlUp).Row
        If Not .Cells(m, 2) = "Thank You" Then
            .Cells(m + 2, 2) = "Thank You"
        End If
    End With

[adam]

I'm sorry Hans. If I may ask how should I modify the above lines If I want the text "Thank You" to appear in the column B but after one row from the last row of text in the column C.

[adam]

I have worked it Hans. Now I'm getting what I wanted. Thanks for the help Hans. I do really appreciate your work.

[adam]

The following code copies the reference range that suits the Product name's age to the sheet "sales" from the sheet "Reference"; when the age is written in the cell "F3".

For example if the accession number is written in the cell B2 the related data from the sheet "data" and sheet "Reference" for that product gets copied into the sheet "Sales".

And the reference range gets changed when either "Male" or "Female" is written in the cell "F3".

How may I adjust the following code so that when the age in the cell "F3" is changed the suitable age of reference range for that particular products gets copied into the column "D" of the sheet "Sales".

Code: Select all

Option Explicit 
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    Dim c As Range, cel As Range 
    Dim Age As Long, L As Long, H As Long, X As Long 
     
    If Target.Count > 1 Then Exit Sub 
    On Error Goto Exits 
     
    Age = Range("F3") 
    If Target.Column = 1 And Target.Row <> 1 Then 
         
        Application.EnableEvents = False 
        With Sheets("Reference") 
            Set c = .Columns(1).Find(Target).Offset(1).Resize(4) 
            For Each cel In c.Offset(, 4) 
                L = Split(cel)(0) 
                H = Split(cel)(2) 
                If Age >= L And Age <= H Then 
                    X = X + 1 
                    cel.Offset(, -4).Resize(, 5).Copy Target(X + 1) 
                End If 
            Next 
        End With 
    End If 
     
Exits: 
    Application.EnableEvents = True 
End Sub 

Workbook with the sheet "reference" is attached for reference.

[HansV]

I'm sorry, Adam. I've told you before that I don't think this application is suitable for Excel.

[adam]

It's Ok Hans. Here's a workbook which does give the reference range for the particular color when the age is written in the cell "F1" and when the color name "blue" is written in the cell "A6". the code copies the range below the text Blue. can this be modified so that when the text either light blue or dark blue is written in the cell A6 and the age is written in the cell F1 the reference range gets copied to the same row instead of the row below.

I'm sorry If my question is repeated with your answer.

Note: the ranges that I have mentioned in my previous post would differ in this sample.

[HansV]

Perhaps somebody else will help you.

[StuartR]

If Hans has finally got to the point where he thinks this can't be done sensibly in Excel, then it is unlikely that the rest of us are going to be able to do this.

[adam]

Hans, I do respect your conclusion & StuartR I do agree with your statement. Anyway, thanks for the reply.

[adam]

How could the text "Thank You" in the following code be made to appear as bold into the sheet mentioned in the code.

Code: Select all

   Dim m As Long
    With Worksheets("Sales")
        m = .Cells(.Rows.Count, 1).End(xlUp).Row
        If Not .Cells(m, 4) = "Thank You" Then
            .Cells(m + 1, 4) = "Thank You"
        End If
    End With

I did use the line

Code: Select all

  Me.Cells(nextRow, "B").Font.Bold = True

But it doesn't work.
I would be happy if you could let me know what I have done wrong in here.

[VegasNath]

Inside the IF .... End If, add the following:

Code: Select all

.Cells(m + 1, 4).Font.Bold = True

[adam]

Nathan, thanks for the reply. I do really appreciate your kind help. With your suggestion the text now appears on the worksheet as bold.

[adam]

How could I make the texts mentioned in the following code to appear as upper case when copied to the worksheet

Code: Select all

nextRow = nextRow + 1
                        Category = .Cells(i, "D").Value2
                        Me.Cells(nextRow, "D").Value2 = Category
                        Me.Cells(nextRow, "D").Font.Bold = True
                        nextRow = nextRow + 1
                        Me.Cells(nextRow, "A").Value2 = "Product Name"
                        Me.Cells(nextRow, "A").Font.Bold = True
                        Me.Cells(nextRow, "E").Value2 = "Sale"
                        Me.Cells(nextRow, "E").Font.Bold = True
                        Me.Cells(nextRow, "F").Value2 = "Units"
                        Me.Cells(nextRow, "F").Font.Bold = True
                        Me.Cells(nextRow, "G").Value2 = "Price Range"
                        Me.Cells(nextRow, "G").Font.Bold = True
                        nextRow = nextRow + 1
                    End If

I did try using the line

Code: Select all

x.Value = UCase(x.Value)

But it does not work.

[HansV]

There is no variable named x in your code, so the line you tried doesn't refer to anything.

If you want the category to be in upper case, change the line

Category = .Cells(i, "D").Value2

to

Category = UCase(.Cells(i, "D").Value2)

If you want text such as "Product Name" to be in upper case, simply change it to upper case in the code.

[adam]

If you want the category to be in upper case, change the line

Category = .Cells(i, "D").Value2

to

Category = UCase(.Cells(i, "D").Value2)

When done so, the code creates category headings for each product instead of one heading for each category.

How may I prevent this?

[HansV]

Does it work better if you use

Code: Select all

                        Category = .Cells(i, "D").Value2
                        Me.Cells(nextRow, "D").Value2 = UCase(Category)

[adam]

Thank you Hans, this modification works very well & I'm happy for that.
Thank you once again.