Posts by bbromley

    I am trying to define a range as a string value to be used in a piece of code. I would like an address, say Sheets1 Cell A1 to be returned as a strng value "Sheets1!A1". I have tried defining the range as a string and using the address function, but I am having no joy.

    Re: Multiple End(xldown)

    Hi Bill,
    Thank you for your time. I have a loop which steps down a series of subheadings and pastes some values in the cells directly below each subheading. After each loop I want the macro to End(xlDown) to the next subheading and then paste its values, so what I am trying to get to is End(xlDown)*i where i is the number of subheadings. The subheadings are at fixed places in the worksheet so I can't select the last row and end up.

    HI there,
    Can anyone please tell me what the relevent code is to achieve multiple End(xlDown). At the moment I am having to do this using End(xlDown).End(xlDown).End(xlDown) and I was wondering if there was a way of simply using End(xlDown)*3?

    Re: Match Function In Vba Returning Error

    Thanks for your comments everyone.
    Just for clarity, the reason I have chosen to use the Match function on a single cell is that it will return either 1 or N/A which means that I can multiply the product of the Matches for each of the three cells in the row and if all criteria are met then I have a positive result. When I get a negative result I get an Error result the macro crashes instead of simply rolling onto the next row and checking for a positive result.
    So to be clear as to my question, I was hoping that someone would be able to tell me how I can get the code to skip past an error reult from the Match functions and continue with the loop. I have tried using an On Error Resume Next but that results in the error being treated as a positive result.

    Hi there,
    I am trying to use a match function in VBA to return a value when a number of values in a row match a certain criteria (I have tried the Find approach on the Ozgrid help pages but it is not what I am after). My problem using a match function of course is that when there is no match the code returns a type mismatch error. I have tried to circumvent the error problem using an "On error resume next" but this then delivers a match for all rows. Anyone have any thoughts about how I can get around this problem?

    Hi there, I am trying to use an ADDRESS function in a sumproduct formula but having no joy getting it to work. Formula works fine if I type in the physical address but gives me a #VALUE when I use the address function. Anyone have any idea how I can work around this?

    =SUMPRODUCT(INDEX('Predicted returns'!$M$7:$AA$136,0,MATCH($C6,'Predicted returns'!$M$5:$AA$5,0)),--((ADDRESS(7,(4+'Input Sheet'!$C$13),,,"Predicted returns")&":"&ADDRESS(136,(4+'Input Sheet'!$C$13)))='Basket position builder'!$C5),--('Predicted returns'!$I$7:$I$136>0),'Predicted returns'!$I$7:$I$136)*(G$7*100)/$E6

    Re: Variable Column Range Definition In Sumproduct Formula

    Daddy, thanks for your reply. You have solved what has been a major headache for me even though, as it turns out, the solution you proposed is quite simple really. Thank you very much for your time, genuinely VERY much appreciated!!

    Re: Variable Column Range Definition In Sumproduct Formula

    Daddy, thank you very much for your reply. Perfect solution. I still need to use the array formula because I have to multiply the outcome by the multiplier, but your core formula worked perfectly - Muchas Gracias Senor.

    Hi there, I am performing a sumproduct calculation in an array form. The first three columns in the data table have criteria, "Name", "Letter" and "Multiplier". The following 5 columns have days of the week, "MON", "TUE" etc.
    What I am trying to achieve is to use a sumproduct array to muliply the "Multiplier" criteria against a particular "Day" criteria when "Name", "Letter", and "Day" criteria match a series of reference cells. What this requires me to do is to define the "Day" range of values differently when I change the "Day" criteria. How do I create a variable range?. I apologise if this is as clear as mud. Simple example file attached.

    Hi there. I am running office 2007. I am using conditional formatting in a pivot table to highlight rows where the value in the last column meets a particular criterion. However everytime I update the table the formatting gets lost. I have looked through all the threads I can find to establish if there is some VBA code that I can use to reapply the required conditional formatting after each update but not having much luck. Can anyone point me in the right direction please?

    Re: Match Cells By Date

    Hi Ger,
    I could do it with a lookup function but for various reasons I need to automate the process of copying and pasting the value next to the corresponding date.

    Hi there, I am trying to do a pretty simple thing but have got myself into a muddle.
    I have a series of dates in a column in sheet2 and would like to copy the contents of a cell on sheet1 and paste it the cell to the right of the date which matches today's date. My attempts to get this to work are shown below. Any help would be VERY much appreciated. Thanks

    Re: Delete Rows Where Cell Value Equals Zero

    Thanks for the reply OKK, but in this case I definately needed a macro with a loop. Firstly it needs to be an automated process,and secondly the table is comprised of various subtables with headings and subtotal and so on. Writing the VBA to sort and then use filters with all the sub-headings and sub-totals getting mixed up seems like a lot more work than simply using a loop function to remove what I don't need. Thanks again.

    Hi there,
    I have a table of data from which I want to delete all rows where the value in column B is zero. I have the following code which doesn't engage at all, but can't figure out what is wrong with it. Any thoughts appreciated.

    Sub Delete_row()
        Dim LastRow As Long, n As Long
        Last_Row = Range("B65536").End(xlUp).Row
        For n = LastRow To 11 Step -1
            If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
        Next n
    End Sub