0

I have an Excel report. This report has many, many different types on it. (A, B, C, D, etc.). I'd only like a few of these types - Let's say A and B. The actuality is much larger, otherwise I'd use an autofilter. I'm trying to move A and B from the first workbook to the second workbook, and leave the rest alone. Here's what I have so far:

Sub ConditionalMoving ()
    Workbooks.Open Filename:=MSCReportPath
    Set MSCReport = ActiveWorkbook
    Workbooks.Open Filename:=CABReportPath
    Set CABReport = ActiveWorkbook

    Set MSTab = MSCReport.Sheets(1)
    LastRowMSC = MSTab.Range("A" & Rows.Count).End(xlUp).Row
    i = 1
    j = 1
    For i = i To i = WorksheetFunction.CountA(MSTab.Range("A1:A" & LastRowMSC))
        If IsNumeric(WorksheetFunction.Match(MSTab.Range("E" & i), Range("MSCFundList"), 0)) Then
            Set RngMSC = MSTab.Range("A" & i & ":AZ" & i                
            CabReport.Sheets("MS").Range("B" & j & ":BA" & j).Value = RngMSC.Value
            j = j + 1

        End If
    Next I
End sub

And it runs no problem. At the end, I have a large block highlighted on my destination sheet - with no text at all. The size of the highlighted block is roughly what I'd expect to come through.

Am I making any obvious mistakes? I've seen some things with arrays floating around, is there a better way to do this with an array? How would you find the needed parts and move them over?

No error messages are generated.

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
Selkie
  • 1,215
  • 1
  • 17
  • 34
  • `If IsNumeric(WorksheetFunction.Match(...)) Then` looks a bit odd - if there's no match then this way of calling Match will lead to a run-time error, not a non-numeric return value. – Tim Williams Aug 31 '17 at 00:09
  • `For i = i To i = WorksheetFunction.CountA(MSTab.Range("A1:A" & LastRowMSC))` - this may not loop over your entire dataset if there are any blanks in ColA. – Tim Williams Aug 31 '17 at 00:12
  • Right - I picked a column that will always have data in it for this report. For the IsNumeric, I was under the impression is worked just like Isnumber - it checks if something is a number and returns a Boolean if It is or isn't a number. Running some quick tests – Selkie Aug 31 '17 at 14:49
  • Is that your exact code or do you have some error handling in effect? – Tim Williams Aug 31 '17 at 14:59
  • This is the exact code - I haven't created any error handling yet. There is a lot more code above and below it doing similar things - opening up workbooks, getting lines, copying them over. I just realized - my Fundlist isn't in the active workbook, which has caused problems before. I guess I need to move it to an array/variable inside of VBA and reference that instead. That'd explain the blanks – Selkie Aug 31 '17 at 16:12
  • I think this is the solution: https://stackoverflow.com/questions/10951687/how-to-search-for-string-in-an-array – Selkie Aug 31 '17 at 19:03
  • I'd avoid using any of the answers there which involve `Filter()`, since that also matches substrings and not just the complete value. – Tim Williams Aug 31 '17 at 19:06

0 Answers0