Extracting Values from colored cells

  • Halfsparrow


    This post has been reported for cross posting. Read the Forum Rules and provide links to all other Forums where this has been posted. Do this before continuing here. You will find that this applies to all Excel Forums

  • I'm not sure if I understood correctly what you want to do. However, have a look at the code in the attached file. In the CommandButton1_Click code, I have defined a range (workRange) that corresponds to the columns associated with the riser number. For example, if you enter HC-01, the variable "workRange" will refer to the range E10:H46. I hope this helps.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Hey mumps, i actually want a dynamic application which takes the opened workbook as a reference and not just Upload.xlsx is it possible?

  • Will the workbook always have the same name?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • No the name of the workbook will be different too

    What i was thinking, that when the user opened excel file in 1st step i have made that sheet and workbook as the active workbook and activesheeet

    so i will add activeworkbook and activesheet in userform2 instead of using names

  • We can make this work without using activeworkbook and activesheet because they can be tricky at times. Will the sheet name always be the same? If so, what is the name? If not, will it always be the first sheet in the workbook? Also, will you have only those two workbooks open when the code runs?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • yes the sheet will be always the 1st sheet. but i have figured that out

    thankyou

    also, when user writes RiserNo it selects the field under that but is it possible that the only that anchor points are displayes that is under that riser number?

    what i mean is if the user enters HC-01 it selects the 4 columns under it but can it also select the red colored cell, green colored cell, blue colored cell and display it as firstanachor, secondanchor


    Also can i get your email id or something so that i can show you there what i actually want?

  • Also can i get your email id or something so that i can show you there what i actually want?

    Forum rules do not allow communication via email because this does not permit all Forum members to participate. Attach a de-sensitized version of your file and explain exactly what you want.

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Okay i will explain you here

    Consider the sheet i attached as the same worksheet

    now i am working on application which will first ask user to open a excel file they wish to open

    once opened another userform will pop up with the fields:


    type of pipe : Combobox with 4 options (DLE, DLE-ERV, DSC, DSC-ERV).

    Heating and cooling temperature. this will be directly pre-populated from opened excel sheet


    Riser number: user will enter the riser number they want to calculate data for


    And 7 fields for anchor points which will also be pre populated according to the riser number


    NOW HOW MY APPLICATION WILL WORK

    Basically it an application for finding the location of an expansion loop in a pipe


    so what i will do is user opens excel file from userform1 make it as active workbook and active worksheet extract temperature data from it, user will write riser number and anchor points for that riser number will be pre populated.


    then on the backend, when clicked calculate button

    i will add the floor heights given in inches between 1st anchor point and 2nd second anchor point, 2nd anchor point and 3rd anchor point, 3rd anchor point and 4th anchor point and so on and store them in different variables. now with the formulae. DeltaL = total height * alpha * change in temperature.


    and compare that deltaL with the loop's value


    This is what i want to build.

    Files

    • Upload (1).xlsx

      (971.57 kB, downloaded 30 times, last: )
  • Have a look at the attached file. I have modified the code a bit. After you enter the riser number, press the RETURN key. Everything seems to be working properly except for the calculations because I really don't understand what the calculations are supposed to do. The code for the calculations would go in the CommandButton1_Click code module. I might be able to help if you could describe step by step, referring to specific cells, rows, columns and sheets how the calculations should work, what the end result of the calculations would be and where you want to put the results. Use the actual values in the "1-2" sheet that you posted to describe the steps.

    Files

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • You are very welcome. :)

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Will it be in only one sheet or can the number exist in more than one sheet?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • If it can exist in more than one sheet, which values do you want to use to populate the text boxes and combobox?

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • If it can exist in more than one sheet, which values do you want to use to populate the text boxes and combobox?

    Everything remains the same, it is just they can't store all the anchor points in one sheet so they add 2-3 anchor points in one sheet and rest of them in other sheet.

  • Try:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.