0

This has been driving me nuts for awhile. What I'm trying to do is, in theory, fairly simple.

I have a list of names I care about. This list of names is in a named range. I'd like to open a report, see if the name in a particular column is on the list, and if it is, move it over. Otherwise, don't move it over. I've tried putting the list in a range, in a variant, just referencing it directly. I've tried loops, conditionals, loops within loops, worksheet functions, no worksheet functions, ubound and lbound, isnumeric, deleting the rows and moving everything over, and more. I just can't get it to work. Current set of code:

Dim i As Long
Dim j As Long
Dim MSCFundList As Range

CABReconFilePath = Range("CABReconFilePath")
Set MSCFundList = Range("MSCFundList")

Workbooks.Open Filename:=CABReconFilePath       
Set CabReport = ActiveWorkbook

Workbooks.Open Filename:=MSCReportPath
Set MSCReport = ActiveWorkbook

Set MSTab = MSCReport.Sheets(1)

LastRowMSC = MSTab.Range("A" & Rows.Count).End(xlUp).Row

j = 2 ' count the row offset in the destination range

'Getting in the headers
Set RngMSC = MSTab.Range("A1:AZ1")
CabReport.Sheets("MS").Range("A1:AZ1").Value = RngMSC.Value

For i = 2 To LastRowMSC
    Debug.Print LastRowMSC
    If IsNumeric(Application.Match(range("E" & I), MSCFundList, 0)) Then
        Set RngMSC = MSTab.Range("A" & i & ":AZ" & i)
        CabReport.Sheets("MS").Range("A" & j & ":AZ" & j).Value = RngMSC.Value
        j = j + 1
   End If

Next

I'm at my wits' end. I've gone over a few different stack overflow posts - the most promising one was Check if a value is in an array or not with Excel VBA

Edit: Expected behavior: Only some rows move over. Actual behavior: No rows move over. Headers are moving over correctly.

Selkie
  • 1,215
  • 1
  • 17
  • 34
  • have you stepped through the code line-by-line and tested the result of each line to see what the expected behavior is not happening? – Scott Holtzman Sep 12 '17 at 21:08
  • I have stepped through - it simply goes through each line, and doesn't move over when I'd expect it to. Should try it with a debug.print statement at each line... Headers are moving over correctly, should put that in. – Selkie Sep 12 '17 at 21:18
  • 2
    `For i = 2 To i = LastRowMSC` should be `For i = 2 To LastRowMSC` – Scott Craner Sep 12 '17 at 21:26
  • @ScottCraner that certainly looks like a problem. – Floris Sep 12 '17 at 21:34
  • Fixed that, fixed a 1004 that generated after, updated code to reflect the changes - still no data is being moved over – Selkie Sep 12 '17 at 21:45
  • 1
    this `"E" & i` inside the match, I assume should be `mstab.Range("E" & i)` – Scott Craner Sep 12 '17 at 21:49
  • Unless you are searching for the string `"E2"`,`"E3"`,... instead of the value in those cells. – Scott Craner Sep 12 '17 at 21:49
  • Yup, just worked that out, was coming here to update that it now all works! Thank you @ScottCraner – Selkie Sep 12 '17 at 22:14

0 Answers0