Posts by clariinc

    Re: Omit rows to copy and paste if cell value does is not numeric

    Hi All,

    I have since written the code below (part of the full code), and it seems to work fine on my excel. However, when run on some other PCs, it produces an 424 error 'object required' at the line in red below.

    does anyone have any idea what's wrong with the code? Basically I am trying to hide the entire row if column 16 of the table contains an error.

    'identify the columns and copy the data


    i have an existing code that copies and pastes filtered visible cells from one table (in sheet 1) to another (in sheet 2).

    however, I would like to add in a criteria such that if the column Q value is not numeric (I.e which is a result of the formula returning an error), then that particular row will be omitted from the copy/paste.

    How can I incorporate this criteria into my vba?


    Re: VBA to copy paste some filtered columns to new sheet (dynamic table)


    As a follow up to my query, may I also check if it is possible to incorporate an 'if' into the vba -- such that as long as any of the filtered visible cells contain "#", then do not copy that row.

    this is because some of the input data may contain errors, in which case i do not want to copy and paste that row.

    thank you!


    I have two existing codes that:

    1) Filters based on multiple criteria (keyed into 'Raw' tab, B3 to F3)
    2) One of the criteria is a dropdown data validation list, which i have used a code to enable multiple selections (see attached file 'Raw' tab, cell D3)

    The issue is that the filter does not work when D3 uses multiple criteria, as excel does not recognize the commas as separate criteria. Is there a way to modify my Filtering macro such that i can overcome this issue?

    It's possible that I may be selecting multiple options in the D3 dropdown and not just 2 values.


    Re: Map text to numeric rankings

    this is a sample and there are more potential variations for every rating :?. Perhaps it should reference to the clean column to check for the closest match? I'm not sure if that is possible or will work..?

    Hi, I have an existing vba see attached and code below that copies and paste columns from one sheet to another. The destination sheet has some existing columns that does a Filldown everytime the macro is run.

    Sub copypaste()

    Dim r As Range
    Dim r2 As Range
    Dim lLast_Row1 As Long
    Dim lLast_Row2 As Long
    'identify the columns and copy the data
    With Worksheets("Raw").ListObjects(1).DataBodyRange
    Set r = Application.Union(.Columns(1), .Columns(2), .Columns(7)).SpecialCells(xlCellTypeVisible)
    End With
    With Worksheets("Output")
    Set r2 = .Range("B8:J8").Resize(.Range("B6").CurrentRegion.Rows.Count)

    r.Copy r2.Cells(1, 1).Offset(-1)

    'extend the formulas
    lLast_Row1 = .Range("E" & Rows.Count).End(xlUp).Row
    lLast_Row2 = .Range("B" & Rows.Count).End(xlUp).Row

    If lLast_Row2 > lLast_Row1 Then
    .Range("E" & lLast_Row1).Resize((lLast_Row2 - lLast_Row1) + 1, 6).FillDown
    End If

    End With
    End Sub

    I have an existing Column E in the 'Output' tab, that has a formula =100%/(COUNTIF(b:b, ">0") applied. This ensures that the % allocation is equally split by default.

    However, sometimes a user may overwrite the cells in Column E with his/her own preferred portfolio allocation. Assuming the user overwrites all the formulas in Column E, my existing macro will not be able to apply the COUNTIF formula anymore as it is all overwritten..

    How can i build the formula into the vba such that it will repply the formula everytime the macro is run (see attached file)?

    thank you!


    I have a set of ratings by Moody's and S&P, but I will need to rank them in order to run some filters/pivots. I can't do a simple VLOOKUP as the raw data is unclean.

    See Sheet 1: Is there a way to modify the raw data in columns B and D (either using formula or coding), such that it can translate into the clean data in columns C and E, eventually arriving at the final ranking in column F?

    Thank you!

    Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

    I actually used the line Set r2 = .Range("B8:J8").Resize(.Range("B6").CurrentRegion.Rows.Count) instead.

    I tried running the macro after changing the entire Column E from my formula to manual percentages, but it simply does a filldown of the number instead of the original formula.

    I believe I will need to incorporate the formula into the vba somehow to ensure it repopulates with the formulas instead.

    Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

    Wow thank you so much!

    It works perfectly now. I hope i am not asking too much, but can i also check if the following is possible:
    - Say I put a formula in Column E (%) =100%/((COUNTIF(B:B, ">0"). Understand that it will be applied to the whole column when macro is run.
    - What if after the macro, I made a manual change to some of the cells in Column E such that is overwrites the formula. E.g. I put in 50%, 20% etc.
    - When I re-run the macro, can the column cells change back to =100%/((COUNTIF(B:B, ">0")?

    Thank you!

    Re: VBA to copy paste some filtered columns to new sheet (dynamic table)

    Hi, Thanks for the prompt reply!

    1. This works in copy and pasting the entire set of data, however if the data in Raw is filtered, the macro still pastes the full 14 rows. Is there a way to edit such that it only copies the filtered data?

    2. Can the vba also incorporate the function of clearing the pasted columns before running the copy-and paste process? E.g. When I run the macro a second time, the information is currently pasted in new rows below those pasted from the first run.

    thanks so much!

    Hi, I have a set of data that I will be using different criteria to filter.

    I will need to

    1) copy some columns of the filtered data without headers, and paste into another existing sheet.

    2) as the pasted sheet has other columns (with formulas), i will need the formulas to extend to all the pasted rows.

    In my attached file, there are 14 possible rows that can be copied over to the Output sheet. I am trying to copy columns A, B and H over to Output. But as you can see I also have formulas in Columns F, H and I in Output. It has to be applied whether the filtered data has 5 or 10 or 14 rows. How can i ensure that the formulas will be applied to however many rows are pasted?

    thanks very much!