0

I have written some VBA code to populate cells in a worksheet. The annoying thing is , is that sometimes it works and sometimes it doesn't for absolutely no reason other than closing down the sheet and opening it again. Here is my code. I get the runtime error 1004 

    Dim m As String
    Dim a As String

    Dim n As Variant

    n = Array("W", "X", "Y", "Z", "AA", "AB", "AC", "AD", "AE", "AF", "AG", "AH", "AI", "AJ", "AK", "AL", "AM", "AN", "AO", "AP", "AQ", "AR", "AS", "AT", "AU", "AV", "AW", "AX", "AY", "AZ", "BA", "BB", "BC", "BD", "BE", "BF", "BG", "BH", "BI", "BJ", "BK", "BL", "BM", "BN", "BO", "BP", "BQ", "BR", "BS", "BT", "BU", "BV", "BW", "BX", "BY", "BZ", "CA", "CB", "CC", "CD", "CE", "CF", "CG", "CH", "CI", "CJ", "CK", "CL", "CM", "CN", "CO", "CP", "CQ", "CR")

    Dim i As Integer
    Dim j As Integer

    Dim adwes As String




    For j = 4 To 100
        For i = 0 To 19

            adwes = n(i) + CStr(j)
            s = "A" + CStr(j)

            m = "='X:\Computing 2018-2019\PLCs\2019 - 2021\KS3\Year 7\[" & Range(s).Value & ".xlsx]USC'!$C" & i


            Range(adwes) = m


        Next i
    Next j
Mixstah
  • 411
  • 1
  • 7
  • 22
  • 1
    Twice in this code you use `Range`.. but you don't specify where the Range is. So Excel is guessing which Workbook and Worksheet you're referring to. – CLR Dec 16 '19 at 12:07
  • Ah ok I need to be specific about the work book and sheet but can you tell me why it sometimes works and others it does not? – Mixstah Dec 16 '19 at 12:09
  • Just a note, concatenating strings is best done through the `&` operator instead of the `+` operator. The latter needs two string values, but the first doesn't. So for example `adwes = n(i) + CStr(j)` can be rewritten to `adwes = n(i) & j`. Also, why did you create such a large array and only plan on using the first 20? – JvdV Dec 16 '19 at 12:13
  • Thanks for the tip JvdV. Also, because the rest of the array are for other sheets in the source table so the line USC will be replaced with KODU etc I will handle this with an array when I get the first part working. – Mixstah Dec 16 '19 at 12:17

1 Answers1

1

Ok my apologies for seemingly wasting your time. The code was sound though poorly written and with the help of you all. It basically came down to the fact of two things 1. The zero reference to another cell thank you JvdV and 2. The cell A(n) used to link to another document was a name and some of the names had O'Brady so the quotation was causing trouble.

Mixstah
  • 411
  • 1
  • 7
  • 22