Posts by halfsparrow

Hello

I am working on a project and first step was to count the number of floors in a particular user enters in the textbox

now i want to check if its is (DSC, DSC-ED, DSC-FA) (DSC, DSC-ED, DSC-FA these are same just different names) or (DSC-ERV, DSC-HRV and this both are same as well just different names) from column I for the each entered floor

what i mean here is if the user entered L2 it shows me the total count of L2 in a particular sheet now i want to check for each L2 weather it is (DSC, DSC-ED, DSC-FA) or (DSC-ERV, DSC-HRV ) from column I and then display the total number of DSC and total number of DSC-ERV in that floor. (DSC-ED, DSC-FA comes under DSC so you can count them or treat them as dsc) (DSC-HRV comes under DSC_ERV so you can treat it as DSC-ERV)

I have attached the file i am using.

Demo Manufacturing file is the demo excel sheet and Manufacturing breakdown is the macro enabled sheet.

If you can please help me with this?

Thank-you

Hey Royuk can you please look into this?

Thanks

Sure, your help is much appreciated.

Hey, can you help me a bit more.

i am in a lot of dilemma here.

I am working on a project and first step was to count the number of floors user enters in the textbox

now i want to check if its is (DSC, DSC-ED, DSC-FA) (DSC, DSC-ED, DSC-FA these are same just different names) or (DSC-ERV, DSC-HRV and this both are same as well just different names) from column I for the each entered floor

what i mean here is if the user entered L2 it shows me the count now i want to check for each L2 weather it is (DSC, DSC-ED, DSC-FA) or (DSC-ERV, DSC-HRV )

and then display the total number of DSC and total number of DSC-ERV in that floor. (DSC-ED, DSC-FA comes under DSC so you can count them or treat them as dsc) (DSC-HRV comes under DSC_ERV so you can treat it as DSC-ERV)

I have attached the file i am using.

If you can please help me with this?

Thank-you

Thankyou so much royUK for your help.

Thanks royUK, your code works too.

Thanky-you so much

Thankyou so much rollis, it worked

Quote from Carim

Hello,

Is this question related to the one you had about colored cells ...?

Counting Colored Cells in particular row in whole workbook

No No its not, its kind of similar but its not related to that.

I have to add other things for calculation in this getting the count is just the beginning.

Hello,

I need a help with a small thing.

I am working on a project I am attaching the sample file as well as the macro enabled file.

My macro enable workbook will first ask user to open the excel file and when opened it calculate some data what i need here

when the 2nd userform pops up it asks user to type in the floor number (It is from column D in Demo file) if the user enters L2 it should show the total count of L2 in that sheet. For example in this excel file if the user enters L2 it should show there are total 12 L2 in a msgbox, if the user enters L3 application should calculate the number of L3 in Column D and show its count.

I have tried it but it always shows 0 and i don't know why.

Please help,

Thanks

Thank you so much royUK

Hello experts i have a macro enabled excel file which ask user to excel file they wish and calculate some required stuffs

i just need some help where if user entered some text in textbox i need to check if that text is in range (e6:h7) and if it is then select the e to h column

else if its in the range(i6:l8) then it should select column i to l

Thanks in advance

Okay thankyou so much

Hello everyone, I am working on a vba project and it is going great. i am facing just a small issue

i have a userform1 that allows user to open excel file

Private Sub CommandButton1_Click()
      
Dim TargetFile As Variant
   
        TargetFile = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Excel Files *.xls* (*.xls*),")

        If TargetFile = False Then
            MsgBox "No file selected.", vbExclamation, "Sorry!"
        Exit Sub

        Else
        Workbooks.Open Filename:=TargetFile
        Unload UserForm1
        UserForm2.Show VBA.FormShowConstants.vbModeless
        

End If

End Sub

This is the code for UserFrom1 it is opening the excel file but it does not show instead it shows the excel file that has been enabled with macro

so i just want to know how do i bring the opened excel file in front or in foreground?

Thanks For your help

Hey Carim, thankyou so much for your help.

i figured a way to work this.

Thank you

Quote from Carim

Have you tested the modified version of the UDF message # 17 ...???

i have its not working

This is my macro enabled excel file

Okay what i meant is

assume this is the excel file. user wants to find how my blue colored cells are there on floor 28 (Column A is for floor) so he will type in L28 in the TextBox1 in UserForm2 and i want an macro that will find the L28 in all sheets and give the total number of blue colored cell. in this particular example if user entered L28 it would be 2 because it has only 1 sheet.

Hope you understand

Okay i will explain you everything,

basically i have a userform1 and its code is:

    Private Sub CommandButton1_Click()
Dim TargetFile As Variant


    ' Open dialogue box to browse for file
        TargetFile = Application.GetOpenFilename _
        (Title:="Please choose a file to open", _
        FileFilter:="Excel Files *.xls* (*.xls*),")


    ' If no file is selected then...
        If TargetFile = False Then
    
    ' Display message box with an error
        MsgBox "No file selected.", vbExclamation, "Sorry!"
    
    ' Exit the macro
        Exit Sub

    ' Else if a valid file is selected then...
        Else
    
    ' Open the selected workbook
        Workbooks.Open Filename:=TargetFile
    
        Unload UserForm1

    With UserForm2
       .Show
    End With
  
End If

End Sub

and it opens an excel file they want. once they open the excel file my UserForm2 pops up which has a textbox1 where user will type the floor number

now what i want is my application to search that floor in Column A of every worksheet in opened workbook and add the number of blue colored cells present in it.

Hope this helps.

Okay Perfect, i didn't understood at first. but now i do

thankyou so much

But is it possible instead of writing the row, user can type in the values in Column A, Like if they want want 7th floor, they can just type 7 instead of seaching for its row number?

Quote from Carim

Sorry ... copied too quickly the code from the macro to the function ...

Could you test the following version

I meant the macro version, it is not working.