Posts by bbromley

Re: Define Range & Sheet Name As String

That's fantastic Dave, thank you.
Norie, the code is simply meant to define cell parameters which are picked up by another macro.

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.

Sub Example()


    Dim rngeA As String
    Dim rngeB As String
    Dim rngeC As String
    Dim rngeD As String
    
    rngeA = Sheets("BB").Range("E2")
    rngeB = Sheets("BB").Range("B1")
    rngeC = Sheets("BB").Range("B2")
    rngeD = Sheets("BB").Range("F3")


'The following line is what I am trying to achieve 
   Call FillWithBBData("BB!E2", "BB!B1", "BB!B2", "BB!F3", True)


'This is what I have been trying
    Call FillWithBBData(" & rngeA.Address & ", " & rngeB.Address & ", " & rngeC.Address & ", " & rngeD.Address & ", True)
    Call FillWithBBData(rngeA, rngeB, rngeC, rngeD, True)


End Sub

Re: Multiple End(xldown)

Thanks very much Bill, a sensible solution.

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?
Thanks

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?

Sub DataSum()
    
    Dim strData As String
    Dim i As Integer
  
    i = 0
    Sheets("Sheet1").Activate
    Range("B4", Range("B4").End(xlDown).Offset(0, 0)).Select
    For Each cell In Selection
        If cell.Value = 0 Then
        i = i + 1
        Else
            strData = Sheets("Sheet1").Range("$B$4").Offset(i, 0)
            myVal1 = Application.Match("A", Range("$C$4").Offset(i, 0), 0)
            myVal2 = Application.Match("B", Range("$F$4").Offset(i, 0), 0)
            myVal3 = Application.Match("C", Range("$I$4").Offset(i, 0), 0)
            myTotal = myVal1 * myVal2 * myVal3
        If myTotal = 1 Then
        Sheets("Sheet1").Range("K2").Offset(i, 0) = strData
        Else
        End If
    i = i + 1
    End If
    Next cell


End Sub

Re: Address Function In Sumproduct Formula

Thanks Daddylonglegs, your solution is absolutely perfect. Very much appreciate your time sir.

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.

Re: Pivot Table Conditional Formatting Lost On Update

Lee, thanks you very much indeed!! I will give it a try.

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

Sub Insert_Number()


Dim DSeries As Range
Dim match As Boolean
match = False
Dim c As Range
 
Sheets("Sheet1").Select
Range("B3").Select
Selection.Copy


Sheets("Sheet2").Select
Do
Set DSeries = Sheets("Sheet2").Range(Range("B2").Range("B65536").End(xlUp))


For Each c In DSeries
    If Date = c.Value Then
        match = True
        Exit For
    End If
Next c


If match Then
    Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Range("A1").Select
End If


End Sub

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.

Re: Delete Rows Where Cell Value Equals Zero

Ger, thanks for spotting that. I can't believe I was so stupid. Option Explicit sounds like a good idea from now on! Thanks again.

Re: Delete Rows Where Cell Value Equals Zero

No, can't sort it. It definately needs a macro.

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