Posts by Dave Edmonds

    Hi, the below code works fine when i say


    activesheet.paste


    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.


    Dave

    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?


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


    Many thanks


    Dave

    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:


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


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


    Code
    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"]

    [tr]


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

    [/tr]


    [/TABLE]


    can anyone assisit please?


    Dave

    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?


    Dave

    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.


    Dave

    Hi,
    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?



    Thanks,


    Dave

    Re: Programatically add validation list to cell


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

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


    with


    Re: Programatically add validation list to cell



    Full code added for clarity.


    Dave

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


    Code
    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

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


    Many thanks


    Dave

    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.


    so

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


    becomes

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


    Cheers
    Dave

    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.


    Dave

    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


    Dave