+ Reply to Thread
Results 1 to 9 of 9

Adding a Row Wise Formula in Filtered Special Visible cells only

  1. #1
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Adding a Row Wise Formula in Filtered Special Visible cells only

    Adding a Row Wise Formula in Filtered Special Visible cells only

    Dear Forum,

    I need to only put a formula in a specific column range based on the values in the other column..
    Now this formula goes like this if there any blanks in the column with the column heading "BWRP Ratio"

    then the "BWRP Ratio" column no -2 i.e "BWRP Amt" -- Col BD in my data needs to have this formula..

    Please Login or Register  to view this content.
    Now , it cannot be BD2 always as the formula needs to be inserted in only the rows fulfilling the criteria in the "BWRP Ratio" COlumn...

    Now in another column, I require just to enter "1" in the column BG in only those row/cells ..


    Please Login or Register  to view this content.
    Regards
    e4excel

  2. #2
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Just an addition...

    It has to be the same row which is visible example there are just three rows which fulfill the criteria and they are 5,7,11..

    So the formula inserted only in these cells ex: col BD5= $BB5 * $BF5 FOR FIFTH ROW...So on so forth...

  3. #3
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Dear Forum,

    Can someone guide me on how to fill the formula till the last visible cell as well as how do i write this formula which involves the same row as the filtered row in the selected celll..

    I have reached here..
    Please Login or Register  to view this content.

  4. #4
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Hi, e4excel,

    maybe try it like this:
    Please Login or Register  to view this content.
    Ciao,
    Holger
    Use Code-Tags for showing your code: [code] Your Code here [/code]
    Please mark your question Solved if there has been offered a solution that works fine for you

  5. #5
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Dear Hahobe,

    Please explain the code I am unable to understand as I am not so good in VBA...

    I am looking to enter a formula in the column BD in all the visible cells based on the filtered condition...
    The formula is the product of two other columns but the same row as the visible row of column BD2..

    Regards
    e4excel

  6. #6
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Hi, e4excel,

    IMO the worst way to learn code is to ask other people to explain it. Good practise is to place the cursor into the procedure in the VBE, make the sheet as well as the VBE visible for you, press F8 and single step through the procedure where the according action may be seen in the Sheet as well if the resolution of the monitor is set accordingly and the commands may be filled with more information as being described.

    You talk about Column BD being filled - if you know it will always be Column BD why do you search for the item in the first sample provided? More important would be the information about the formula to be written: will it always be 2 columns to the left and 2 columns to the right of the found column?

    I altered the resize command to include the column 2 columns to the right of the found item column:
    Please Login or Register  to view this content.
    Ciao,
    Holger

  7. #7
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Dear Hahobe,

    I am trying hard but in vain to decipher the code...

    After a lot of googling I came close but not still what i need as I want this range to come out automatically...

    Please Login or Register  to view this content.
    The Range which I have manually entered BD2:BD642 needs to be automatic...
    Thanks to you I could use your R1C1 reference style formula...

    So how do I keep this automatic range...?

    My number of columns are more but the count can be easily based on the column A which would always be filled with data..SO in this Sheet there are 642 records..


    Regards
    e4excel

  8. #8
    Registered User
    Join Date
    11-03-2008
    Location
    India
    MS-Off Ver
    Excel 2010
    Posts
    2,521

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Dear hahobe,

    I think I got what i wanted now...

    Please Login or Register  to view this content.
    Regards
    e4excel

  9. #9
    Forum Guru HaHoBe's Avatar
    Join Date
    02-19-2005
    Location
    Hamburg, Germany
    MS-Off Ver
    work: 2016 on Win10 (notebook), private: 2019 on Win10 (desktop), 2019 on Win11 (notebook)
    Posts
    8,197

    Re: Adding a Row Wise Formula in Filtered Special Visible cells only

    Hi, e4excel

    I am trying hard but in vain to decipher the code...
    Except from commenting each single line there is only the option to copy each command from the VBA Help - I wonīt do that in any free help forum (and I canīt because Iīm afraid your German will not be good enough to understand ).

    Sorry but why loop through all empty cells and work on them independly if itīs possible to add the formula for all of these cells at once?

    Ciao,
    Holger

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  2. Replies: 8
    Last Post: 07-14-2012, 10:22 AM
  3. Go to > Special > Visible cells only
    By Epinn in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 08-09-2006, 01:44 PM
  4. [SOLVED] Help to adapt Formula syntax to work with Visible Filtered Cells
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 09-16-2005, 07:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1