Posts by Dave Edmonds

    Hi, the below code works fine when i say


    but when i replace it with

    ActiveSheet.PasteSpecial xlPasteValues
    nothing pastes. I have stepped through and it works until I get to the paste line.

    Any help would be appreciated.


    Hi, the below code copies data into a list object. The list object has some formula columns and when the data copies over, it loses its formatting. Can anyone help me to preserve formatting please?

    For Each lobj In sht_inv.ListObjects
                lobj_name = lobj.Name
                temp_rng_strt = Application.WorksheetFunction.VLookup(Client, ThisWorkbook.Worksheets("LOOKUPS").Range("J2:P100"), 5, False)
                wb.Sheets("Financial Data").Activate
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Next lobj

    Many thanks


    Hi, I have some code that copies data from one worksheet to another but the columns move from time to time.
    My solution is a matrix inanother worksheet where the column references sit.

    I now need to call the relevant cell refence that has the range string in it but I am getting an error.

    The string sits here:

    copy_rng_string = ThisWorkbook.Sheets("Data Matrix").Range("O4").Value

    The code that calls the string is erroring with application or object defined error

    lr = ActiveWorkbook.Sheets("Air Data").Cells(Rows.Count, "E").End(xlUp).Row
    ActiveWorkbook.Sheets("Air Data").Range(copy_rng_string & lr).AdvancedFilter Action:=xlFilterCopy, CopytoRange:=ActiveWorkbook.Sheets("Temp_ list").Range("A" & CStr(LR_temp)), unique:=True

    The string format iti s calling is :
    [TABLE="width: 64"]


    [TD="class: xl65, width: 64"]"C2:E"[/TD]



    can anyone assisit please?


    HI, I am having an issue with counting the visible rows after an autofilter has been applied.

    lr works fine but vis_lr is not the right count.

    Can anyone assist in solving this please?


    Re: How to find and replace words in VBA? Multiple words to be replaced and many time

    Set up a 2 column lookup table with old name and new name columns.

    Set a variable to type string called new_name.

    Obviously lookup_table needs to be changed to refer to your table.

    sorry its messy but its late and I am not at my pc.

    let me know how you go on.


    I have need to dynamically call a subroutine based on some parameters that are entered in a worksheet.
    Based on the parameters, the name of the subroutine is returned as variable "substring".
    I am struggling to then call the matching subroutine to that string.
    Can anyone help please?



    Re: Programatically add validation list to cell

    fixed it with the macro recorder (why didn't i use it earlier??
    I replaced

    ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="=" & rlist


    Re: Programatically add validation list to cell

    Full code added for clarity.


    HI, I have the following code that is not quite working and I wonder if you guys can help me tweek it?

    defaultValue = "FERRY ONLY Origin & Destination Search"
    ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="=" & "Reports"
    ActiveCell.Value = defaultValue

    I am trying to add the named range "Reports" as an in cell drop down to the active cell with the default value as the parameter "DefaultValue"
    I get an object error on the line

    ActiveCell.Validation.Add Type:=xlValidateList, Formula1:="=" & "Reports"

    Many thanks


    Re: go to cell of match result

    fixed it:
    I needed to add .range to the end of every criteria when referring to the list columns.


    result = Application.WorksheetFunction.SumIfs(.ListColumns("Net"), .ListColumns("Year_Month"), x, .ListColumns("Booking Type"), x.Offset(booking_type_int, 0))


    result = Application.WorksheetFunction.SumIfs(.ListColumns("Net").Range, .ListColumns("Year_Month").Range, x, .ListColumns("Booking Type").Range, x.Offset(booking_type_int, 0))


    Re: go to cell of match result

    Hi, ok so attempt 2 is below but I am getting a type mismatch on the sumifs line.

    I have tested each of the conditions individually and they seem fine.


    Re: go to cell of match result

    Hi, I am trying to:
    lookup a value (x) in match_range and find the corresponding offset cell in another sheet. I have totally messed up this code so I will do take 2 and if that doesn't work, come back to you.

    Many thanks