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

With Worksheets("ABC").ListObjects(1).DataBodyRange
    
      .EntireRow.Hidden = False
         On Error Resume Next
         Set MyRange = .Columns(16).SpecialCells(xlCellTypeFormulas, 16).EntireRow
         On Error GoTo 0
         If Not MyRange Is Nothing Then ' this line
            MyRange.EntireRow.Hidden = True
            
        End If
        Set MyRange = Nothing

Hi,

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?

Thanks!
Clariinc

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

I would like to apply it to the last column. I agree with your filtering method, however as my actual spreadsheet already has a number of fields that require filtering, I am trying not to complicate it further.

Appreciate any suggestions on this!

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

Hi,

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!

Re: Filter based on multiple criteria separated by commas

Hmm sorry but it does not seem to work when i replaced the FilterMe code with the above. The filter still produces no results when run with multiple criteria in cell D3/L3

Hi,

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.

Thanks!

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)

r2.Clear
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!

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

Ok got it, thanks very much for the kind help!
-Clari

Hi,

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!
Clari

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!forum.ozgrid.com/index.php?attachment/71240/