0

I'm doing some data processing, and I want to achieve this:

A 1

B 36

C 0

D 36

...

To achieve this, I came up with a formula which I want to loop down one column :

Range("C2").Select
    ActiveCell.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],'incident_summary - Jan.csv'!R3C2:R11C3,2,FALSE),0)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C11"), Type:=xlFillDefault
Range("C2:C11").Select

This formula uses vLookup to look for the data point in the source workbook, all of which are located in the same folder on my shared network. As the source workbook may not contain all the data points, ifError is set to return the number for that data point as 0 in the destination workbook.

As you can tell from above, I have 10 data points per month, which I need to fill up for 12 months in one column.

But is I cannot understand how can I loop this formula while changing 'incident_summary - Jan.csv' to Feb, Mar, Apr, etc... as I need to loop this formula for a whole year aka Jan-Dec so any suggestions?

imjellybrah26
  • 15
  • 4
  • Put your months in an array and loop through that replacing the hard-coded name with the array element. – SJR Apr 04 '18 at 09:43
  • And you can reduce to one line `Range("C2:C11").FormulaR1C1 =...` – SJR Apr 04 '18 at 09:45
  • Hello @SJR I have edited my question for clarity, and will appreciate if you're able to demonstrate what you mean by typing out the code, as I'm new to vba coding and am still trying to figure my way around. Much thanks in advance! – imjellybrah26 Apr 04 '18 at 09:48

2 Answers2

0

Well first of all I recommend to avoid using .Select. Then you cannot use Autofill for that, you will need to loop through the range and write each formula on it's own.

So put month names into an array and loop through that array writing the formula in each cell:

Option Explicit

Public Sub FillFormula()
    Dim ArrMonth As Variant
    ArrMonth = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec")

    Dim i As Long
    For i = LBound(ArrMonth) To UBound(ArrMonth)
        Range("C2:C11").Offset(i * 10, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1],'incident_summary - " & ArrMonth(i) & ".csv'!R3C2:R11C3,2,FALSE),0)"
    Next i
End Sub

Note that you are trying to access a CSV file which is not possible. Formulas can only access Excel files. But a CSV is basically a TXT file with comma separated values in it. So there are no sheets nor columns or rows you can access with a formula. It is necessary to convert that CSV into an Excel file first before you can access it with formulas.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Hello, thanks for the reply! This formula doesn't quite work for me. I have 10 data points per month to be filled up via a single workbook. however, this formula only extracts the first value from the first workbook, and so on so forth. meaning for Jan, data points 2-10 show up as 0, and the first two data points in Feb also appear as 0. – imjellybrah26 Apr 04 '18 at 10:06
  • @imjellybrah26 well see my edit. You can add additional columns with formulas for each month like i showed by counting the column in `Offset(row, column)` upwards. If this doesn't help please add screenshots of your sheets into your question so we can see what it should look like. – Pᴇʜ Apr 04 '18 at 10:12
  • @imjellybrah26 I changed the answer. See if that helps. – Pᴇʜ Apr 04 '18 at 11:13
  • hello, your solution works ! Thank you so much for taking the time to help me :) However the code does not work when I specify the whole file path, as the folder containing my source workbooks are in a shared network. I copied the file path from Windows Explorer so it should not be wrong. The formula including filepath goes something like: Range("C2:C11").Offset(i * 10, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], '\\network\group\org\source\incident_summary - " & ArrMonth(i) & ".csv!R3C2:R11C3,2,FALSE),0) " – imjellybrah26 Apr 05 '18 at 01:58
  • @imjellybrah26 Well the issue is that you are trying to use a CSV file. But a CSV is basically a TXT file with comma separated values in it. So there are no sheets nor columns or rows you can access with a formula. It is necessary to convert that CSV into an Excel file first before you can access it with formulas. – Pᴇʜ Apr 05 '18 at 06:17
0

To illustrate my comment above as requested.

Sub x()

Dim months As Variant, i As Long, r As Range

months = Array("Jan", "Feb", "Mar") 'etc
Set r = Range("C2:C11")

For i = LBound(months) To UBound(months)
    r.FormulaR1C1 = _
        "=IFERROR(VLOOKUP(RC[-1],'incident_summary - " & months(i) & ".csv'!R3C2:R11C3,2,FALSE),0)"
    Set r = r.Offset(10)
Next i

End Sub
SJR
  • 22,986
  • 6
  • 18
  • 26
  • well `Range("C2:C11").offset(,i)` should be `Range("C2").offset(i,0)` see my answer. – Pᴇʜ Apr 04 '18 at 09:53
  • Hm are you sure? The opening line suggests he is moving across columns, but then talks of 12 months in one column so you may be right. I'll leave until OP clarifies or he can just accept your answer. – SJR Apr 04 '18 at 09:55
  • Well, yes you might be right: "*loop down one column*" is not very clear – Pᴇʜ Apr 04 '18 at 09:57
  • Hello, to clarify, I want to loop the data such that at the destination workbook the data is filled down one column (My vlookup is programmed to fill up only one column) – imjellybrah26 Apr 04 '18 at 10:02
  • Can you try the two solutions and see which (if either) works as we have interpreted your question differently and still not sure what you are after. Should column C all be Jan, D Feb etc or should C2 be Jan, C3 Feb etc? – SJR Apr 04 '18 at 10:08
  • Jan occupies 10 rows, C2:C11, Feb occupies 10 rows, C12:C21, Mar occupies 10 rows, C22:C31, and so on – imjellybrah26 Apr 04 '18 at 10:12
  • Ha we were both wrong. I've amended the code above. – SJR Apr 04 '18 at 10:20
  • hello, I've adopted PEH's solution ! Thank you so much for taking the time to help me :) However his code does not work when I specify the whole file path, as the folder containing my source workbooks are in a shared network. I copied the file path from Windows Explorer so it should not be wrong. The formula including filepath goes something like: Range("C2:C11").Offset(i * 10, 0).FormulaR1C1 = "=IFERROR(VLOOKUP(RC[-1], '\\network\group\org\source\incident_summary - " & ArrMonth(i) & ".csv!R3C2:R11C3,2,FALSE),0) ". I'm wondering if you know any fix? – imjellybrah26 Apr 05 '18 at 02:15