Extracting Values from colored cells

  • Try:

    It shows object doesn't support this property. :(

  • Which line of code is highlighted when you click "Debug"? Also, when you respond, please don't paste anything from the previous post. This avoids clutter.

    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.

  • Which line of code is highlighted when you click "Debug"? Also, when you respond, please don't paste anything from the previous post. This avoids clutter.

    It fine i figured it out. Thakyou

    It was just " For each ws in ActiveWorkBook.Worksheets" instead of "For each ws in ActiveWorkBook(srcWB)"


    Thank-You

  • My pleasure. :)

    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.

  • My pleasure. :)

    Mumps i need you again, so sorry i keep asking your help but i need this project to be done in few days.

    Actually now i want to add the floor heights in column C from 1st anchor to blue colored single cell (That is loop location) in each HWS CHWS HWR and CHWR and store it in variable with same name that is hws chws hwr and chwr

    it should be done when clicked on calculate button.

    Can you please help me with this?


    Thank-You

  • Please attach updated versions of both files. Make sure they include all the user forms and macros including any code that you have added.

    Actually now i want to add the floor heights in column C from 1st anchor to blue colored single cell (That is loop location) in each HWS CHWS HWR and CHWR and store it in variable with same name that is hws chws hwr and chwr

    Also please explain what you want to do as above with an example from your data using specific cell addresses .

    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.

  • Please attach updated versions of both files. Make sure they include all the user forms and macros including any code that you have added.

    Also please explain what you want to do as above with an example from your data using specific cell addresses .

    Final.xlsm is the macro userform and uload(1).xlsx is the excel file


    Now i am creating another sub when the calculate button is clicked

    in that i need the addition of floor heights given in inches from first anchor point to the light blue colored single cell for HWS. same thing for CHWS HWR and CHWR

    So for example for HWS i need 116.25 + 116.25 +116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25

    For CHWS i need 116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25

    and save the result in varible named 1hws, 1chws, 1 hwr, 1chwr

    variable has 1 because i will need to save this data for 2nd anchor point, 3rd anchor point and so on.


    Thank- you

    Files

    • Final.xlsm

      (35.98 kB, downloaded 40 times, last: )
    • Upload (1).xlsx

      (971.57 kB, downloaded 32 times, last: )
  • I think I understand how you got:

    for HWS i need 116.25 + 116.25 +116.25 + 116.25 + 116.25 + 116.25 + 116.25 + 116.25

    They are the values for C28:C35 and that total would be saved in the variable 1hws for the first anchor. Is this correct? How would you calculate the total for 2hws which would be for the second anchor?

    Since the anchor points can be in different sheets, how do you want that to be handled?

    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.

  • I think I understand how you got:

    They are the values for C28:C35 and that total would be saved in the variable 1hws for the first anchor. Is this correct? How would you calculate the total for 2hws which would be for the second anchor?

    Since the anchor points can be in different sheets, how do you want that to be handled?

    I have the anchor point location saved in the textbox, maybe i can use that as a reference and add the values.

  • You can give that a 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.

  • I have tried this but i cannot reference the variables in another subprocedure in this sub procedure

  • i need the addition of floor heights given in inches from first anchor point to the light blue colored single cell

    The first anchor point is in cell E28 (red). This cell is above the light blue cell in column E which is in cell E36. The total for the first anchor is calculated by adding the values in C28:C35. The second anchor is in E40 (green). What range of values in column C would you add to get the total for the second anchor? The third anchor is in E43 (dark blue). What range of values in column C would you add to get the total for the third anchor?

    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.

  • The first anchor point is in cell E28 (red). This cell is above the light blue cell in column E which is in cell E36. The total for the first anchor is calculated by adding the values in C28:C35. The second anchor is in E40 (green). What range of values in column C would you add to get the total for the second anchor? The third anchor is in E43 (dark blue). What range of values in column C would you add to get the total for the third anchor?

    In this particular case it does not have loop after 2nd and 3rd anchor so it can ignore and do nothing if it does not find the light blue colored cell

  • Are you saying that the code should look only for anchors that are above the light blue colored cells and ignore the anchors below? Since the anchors can be in more than one sheet, will there be light blue colored cells in any of the other sheets?

    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 i mean it should look for the anchor point in the riser number that user has entered and look for the blue colored cell if it is present it should add the floor height from the anchor point to the blue colored cell. and about the blue colored cell in different sheet, yes it would be present but it will always have a anchor point above it

  • Are you saying that the code should look only for anchors that are above the light blue colored cells and ignore the anchors below? Since the anchors can be in more than one sheet, will there be light blue colored cells in any of the other sheets?

    No i mean it should look for the anchor point in the riser number that user has entered and look for the blue colored cell if it is present it should add the floor height from the anchor point to the blue colored cell. and about the blue colored cell in different sheet, yes it would be present but it will always have a anchor point above it


    Can you please help me with this?

  • Are you saying that the code should look only for anchors that are above the light blue colored cells and ignore the anchors below? Since the anchors can be in more than one sheet, will there be light blue colored cells in any of the other sheets?

    We could also just take the anchor points value from the text box and add the values till lightblue colored cell and if it finds Green color before the light blue color it will just do nothing and return no loop found. (For 1st anchor)

    Similarly can code for second anchor.

    I really need your help with this.


    Thank you

  • I have managed to add the values till lightblue colored cell for the first anchor point which is red. The difficulty I am having is adding the values for the other anchor points if they are in a different 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.

  • I have managed to add the values till lightblue colored cell for the first anchor point which is red. The difficulty I am having is adding the values for the other anchor points if they are in a different sheet.

    Dont worry i will figure that out,

    can you please send me whatever you have?

    Thankyou mumps, thank-you so much