Compile error invalid or unqualified reference vba

I'm trying to create excel template (the volume of data will be different from case to case) and it looks like this: In every even row is "Customer" and I would like to put in every odd row "Ledge...

I’m trying to create excel template (the volume of data will be different from case to case) and it looks like this:

enter image description here

In every even row is «Customer» and I would like to put in every odd row «Ledger». Basically it should put «Ledger» to every odd row until there are data in column C. I have this code:

'========================================================================
' INSERTING LEDGERS for every odd row (below Customer)
'========================================================================

Sub Ledgers()

    Dim rng As Range
    Dim r As Range
    Dim LastRow As Long

    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("C5:C" & LastRow)

    For i = 1 To rng.Rows.Count
        Set r = rng.Cells(i, -2)
        If i Mod 2 = 1 Then
            r.Value = "Ledger"
        End If

    Next i

End Sub

But it gives me an error msg Invalid or unqualified reference. Could you advise me, where I have the error, please?

Many thanks!

Pᴇʜ's user avatar

Pᴇʜ

55.8k9 gold badges49 silver badges72 bronze badges

asked Sep 8, 2017 at 12:35

Srpic's user avatar

1

If a command starts with . like .Cells it expects to be within a with statement like …

With Worksheets("MySheetName")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("C5:C" & LastRow)
End With

So you need to specify the name of a worksheet where the cells are expected to be in.

Not that it would be a good idea to use Option Explicit at the top of your module to force that every variable is declared (you missed to declare i As Long).

Your code could be reduced to …

Option Explicit 

Public Sub Ledgers()
    Dim LastRow As Long
    Dim i As Long

    With Worksheets("MySheetName") 
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        'make sure i starts with a odd number
        'here we start at row 5 and loop to the last row
        'step 2 makes it overstep the even numbers if you start with an odd i
        'so there is no need to proof for even/odd
        For i = 5 To LastRow Step 2 
            .Cells(i, "A") = "Ledger" 'In column A
            '^ this references the worksheet of the with-statement because it starts with a `.`
        Next i
    End With
End Sub

answered Sep 8, 2017 at 12:39

Pᴇʜ's user avatar

PᴇʜPᴇʜ

55.8k9 gold badges49 silver badges72 bronze badges

0

Just loop with a step 2 to get every other row in your indexer variable.

Sub Ledgers()
    Dim rng As Range
    Dim LastRow As Long

    LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, "C").End(xlUp).Row
    Set rng = ActiveSheet.Range("C5:C" & LastRow)

    For i = 1 To LastRow step 2
        rng.Cells(i, 1) = "Ledger" 'In column A
    Next i
End Sub

answered Sep 8, 2017 at 12:43

Rik Sportel's user avatar

Rik SportelRik Sportel

2,6511 gold badge13 silver badges23 bronze badges

3

snipe, спасибо, j исправил.
про объявление переменных учту, но сейчас главное что бы программа работала корректно, оформлять код буду позже.

Так, всем интересующимся рассказываю, как должен работать макрос:
действующие столбцы: B, G, H.
просматриваем столбец B на одинаковые значения (они, повторяясь, сначала увеличиваются, потом уменьшаются).
считаем среднее арифметическое соответствующих значений в столбца G (у строк которых значение столбца B одинаковое). Далее вывод результата среднего арифметического в столбец H.

после нескольких дней одиночного мозгового штурма у меня получился вот такой код…

Visual Basic
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
Option Base 1
Sub M2()
 
Dim i As Integer
Dim j As Integer
Dim n As Integer
Dim arrA() As Single
Dim s As String
 
Sheets("ID_1").Select
 
s = "(" & Format(Cells(2, 7), "#,###0.000") & ")"
j = 1
ReDim Preserve arrA(1)
arrA(j) = Cells(2, 2)
n = 1
 
For i = 2 To 10
For j = LBound(arrA) To UBound(arrA)
If Cells(i, 2) <> arrA(j) Then
    ReDim Preserve arrA(j + 1)
    arrA(j) = Cells(i, 2)
Else:
    s = s & "+" & "(" & Format(Cells(i + 1, 7), "#,###0.000") & ")"
    n = n + 1
End If
    
Cells(15 + i, 2) = i
Cells(15 + i, 3) = j
Cells(15 + i, 4) = arrA(j)
Cells(15 + i, 5) = s
    
Cells(i, 8) = "(" & s & ")" & "/" & n
 
Next j
Next i
 
End Sub

объясняю. использовал в качестве фильтра (что б не повторялись значения столбца B) динамический массив. т.е. сначала в нём хранится значение первой значимой ячейки столбца B. потом, при нахождении в столбце B ещё одного нового значения — кол-во эл-тов массива увеличивается и последний элемент принимает новое значение из столбца B. далее снова проверка на схожесть.

наверное, можно было и без массива обойтись, просто как-то отсортировать столбец B с зависимыми значениями столбца G.
ещё была идея сделать двумерный массив типа String, первой мерой которого будет являться тот самый исключающий массив, а второй мерой — строка вычисления среднего арифметического.

не получилось корректно реализовать заполнение исключающего массива и вывод среднего арифметического

как-то так. возможно, ещё как предлагал в теории Hugo121, проще переписать код с другим алгоритмом
буду благодарен а любую помощь и дельные советы.

черновой вариант листа с данными приложил. ещё прикреплён скрин с ответом. так должны выглядеть данные в столбце H после использования макроса (можно и сразу значение среднего арифметического).

вообще макрос будет работать на 4-х страницах, по ~2000 строк в каждой. а сейчас просто тест, 1 страница с десятью строками данных.

  • #2

Sorry the line of code highlighted when i get the error is

Code:

Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _

  • #3

Hello,

When I run this code I get a compile error that says «Invalid or unqualified reference» when I looked into it the error seems to be saying that I am not using a With statement properly. But I am not using a with statement at all.

Has anyone got any suggestions?

Code:

Sub deletingdata()
 
Dim i           As Integer
Dim j           As Integer
Dim WS          As Worksheet
Dim toprow      As Range
Dim lastrow     As Range
Dim AFcolumn    As Range
 
i = 0
For Each WS In Worksheets
    i = i + 1
    Sheets(WS.Name).Activate
    
    If i <> 1 Then
    
        Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        On Error Resume Next
        Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.UsedRange
        Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row
                        
        Set AFcolumn = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Column
                        
        lastrow = Range("AFCol & 65536").End(xlUp).Row
        For j = lastrow To toprow Step -1
        If Cells(j, AFcolumn).Value <> "00*555" Then Cells(j, o).Delete shift:=xlUp
        Next j
        
    End If
    
End Sub

Thanks in advance!

I believe that after your End If and before your End Sub you need a Next.

  • #4

Hello,

Thanks for the reply, looking at the macro now I see that you are correct for it to run properly I do need another next.

However, the bad news is I still have the same error appearing when I run this macro with the same line of data highlighted.

Any other suggestions?

  • #5

Lastrow should be dimmed as Long
Also, shouldn’t this line be

Code:

lastrow = Range("AFCol & 65536").End(xlUp).Row

Code:

lastrow = Cells(Rows.Count, "AFCol").End(xlUp).Row

AND
I think AFCol should be AFColumn !
If you choose Option Explicit in your VBA preferences it would eliminate or identify a lot of syntax errors for you.

Last edited: Sep 24, 2010

  • #6

Thanks for the help guys, Michael I will look into option explicit.

I’m sorry to say though I am still getting the same fault code, if anyone is able to suggest how to fix it, that would be much appreciated.

It says «Compile error: Invalid or unqualified reference»

and the line

Code:

Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row

is highlighted

anyway here is the rest of the code…

Code:

Sub DeletingExcessData()
Dim i           As Integer
Dim j           As Integer
Dim WS          As Worksheet
Dim toprow      As Range
Dim lastrow     As Long
Dim AFcolumn    As Range
i = 0
For Each WS In Worksheets
    i = i + 1
    Sheets(WS.Name).Activate
    
    If i <> 1 Then
    
        Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
            :=xlByColumns, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
        
        On Error Resume Next
        Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        ActiveSheet.UsedRange
        Set toprow = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Offset(0, 3).Row
                        
        Set AFcolumn = .Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                            SearchDirection:=xlNext, MatchCase:=False).Column
                        
        lastrow = Cells(Rows.Count, "AFCol").End(xlUp).Row
        For j = lastrow To toprow Step -1
        If Cells(j, AFcolumn).Value <> "00*555" Then Cells(j, o).Delete shift:=xlUp
        Next j
        
    End If
    
    Next
    
End Sub

Thanks again for the help people!

  • #7

The most probable reason for this error is because the Find isn’t, well, finding anything.

Also why are toprow and AFColumn declared as Ranges? Isn’t just the integer value you want.

And why is ActiveSheet.UsedRange just kind of sitting in the middle, it must be feeling left ouy.:)

Try this, it compiles and runs but I don’t know if it does what you want — nothing to test it on.

Code:

Option Explicit
 
Sub deletingdata()
Dim I As Long
Dim J As Long
Dim ws As Worksheet
Dim rngFnd As Range
Dim toprow As Long
Dim lastrow As Long
Dim AFcolumn As Long
 
    For Each ws In Worksheets
 
        I = I + 1
 
        If I <> 1 Then
            With ws
                .Range("D:D").Cells.Replace What:="nan", Replacement:="", LookAt:=xlPart, SearchOrder _
                                                                                          :=xlByColumns, MatchCase:=False
                '.Columns("D:D").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
 
                Set rngFnd = .UsedRange.Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                             SearchDirection:=xlNext, MatchCase:=False)
 
                If Not rngFnd Is Nothing Then
                    toprow = rngFnd.Offset(0, 3).Row
                End If
 
                Set rngFnd = .UsedRange.Find(What:="ActiveFaults", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByColumns, _
                                             SearchDirection:=xlNext, MatchCase:=False)
 
                If Not rngFnd Is Nothing Then
                    AFcolumn = rngFnd.Column
                End If
 
                lastrow = .Cells(Rows.Count, AFcolumn).End(xlUp).Row
 
                For J = lastrow To toprow Step -1
                    If .Cells(J, AFcolumn).Value <> "00*555" Then
                        .Cells(J, 0).Delete shift:=xlUp
                    End If
                Next J
 
            End With
 
        End If
 
    Next ws
 
End Sub

  • #8

Wow you guys are running rings around me :rolleyes:

Thanks for your help Norie, it doesn’t do exactly what I am after, but as you said you do not have any data.

Anyway I think that I should be able to play around with it to make it do what I need with some slight alterations.

Thanks for everyone’s contributions!

  • #9

Surely the .Find commands are the unqualified references? The compiler is complaining that there isn’t a With statement so it doesn’t know what to stick in front of .Find to qualify it completely.

Should it be just plain Find?

  • #10

Ruddles

Did you try Find just on it’s own?

Won’t work I’m afraid — Sub or Function not defined…

Though you are right about the Find not referring to anything.

That would have kicked off a compile error, I thought the OP was getting a run-time error.

That’s why I said the Find wasn’t finding anything.

I think the code I posted dealt with both problems, the one you point out and the possible problem I spotted.:)

  1. 11-09-2015, 04:33 AM


    #1

    mso3 is offline


    Forum Contributor


    getting compile error invalid or unqualified reference

    Hi friends,

    I want to send mails to all with attachment store in �OUTPUT� folder. I�m getting compile error invalid or unqualified reference on the following line

    Any help will be highly appreciated.

    Thanking you in anticipation.


  2. 11-09-2015, 05:24 AM


    #2

    Re: getting compile error invalid or unqualified reference


  3. 11-09-2015, 05:24 AM


    #3

    Re: getting compile error invalid or unqualified reference

    Hi,

    You are missing

    Dim outmail As Object
    Set outmail = aOutlook.CreateItem(0)

    With outmail
    ‘wrapped around your If Len(Dir(strFileName)) > 0 Then…..Set aEmail = Nothing
    End With

    Richard Buttrey

    RIP — d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.


  4. 11-09-2015, 05:27 AM


    #4

    Re: getting compile error invalid or unqualified reference

    @ Richard

    It’s there, but it is all over the place.


  5. 11-09-2015, 08:36 AM


    #5

    mso3 is offline


    Forum Contributor


    Re: getting compile error invalid or unqualified reference

    Oof! What a silly mistake. Thank you for pointing out mistake. Now it’s working fine.

    Thank you and have a nice day.


If a command starts with . like .Cells it expects to be within a with statement like …

With Worksheets("MySheetName")
    LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
    Set rng = .Range("C5:C" & LastRow)
End With

So you need to specify the name of a worksheet where the cells are expected to be in.

Not that it would be a good idea to use Option Explicit at the top of your module to force that every variable is declared (you missed to declare i As Long).

Your code could be reduced to …

Option Explicit 

Public Sub Ledgers()
    Dim LastRow As Long
    Dim i As Long

    With Worksheets("MySheetName") 
        LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row

        'make sure i starts with a odd number
        'here we start at row 5 and loop to the last row
        'step 2 makes it overstep the even numbers if you start with an odd i
        'so there is no need to proof for even/odd
        For i = 5 To LastRow Step 2 
            .Cells(i, "A") = "Ledger" 'In column A
            '^ this references the worksheet of the with-statement because it starts with a `.`
        Next i
    End With
End Sub

  • Home
  • VBForums
  • Visual Basic
  • Office Development
  • Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Reference

  1. Apr 8th, 2019, 01:19 PM


    #1

    Nish12 is offline

    Thread Starter


    Member


    Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Reference

    Hi,

    I’m using the below vba code to run a Goal Seek macro in my excel file. AC3 is a formula which is calculating a gross margin % (gross margin/revenue), X3 is the gross margin % I want it to solve for that the user can specify in a cell in my model such as 40% and Y9 is the cell that I want it to solve for the value in.

    This code used to execute the goal seek previously but now every time I run it I get a 400 error message or a Compile Error message saying there is an invalid or unqualified reference. And it points to the .Range portion of the formula that is after GoalSeek Goal. How do I get past this error message, I’m new to VBA and am really confused as to how to fix.

    VBA Code:

    Sub SeekIt()

    Range(«AC3»).GoalSeek Goal:=.Range(«X3»).Value, ChangingCell:=.Range(«Y9»)

    End Sub

    I’ve googled this error and even tried using the below code but still got the same error messages

    For example:

    Sub SeekIt()
    With Worksheets(«Sheet1»)
    .Range(«A1»).GoalSeek Goal:=.Range(«A2»).Value, ChangingCell:=.Range(«A3»)
    End With
    With Worksheets(«Sheet2»)
    .Range(«B1»).GoalSeek Goal:=.Range(«B2»).Value, ChangingCell:=.Range(«B3»)
    End With
    End Sub

    Open to any feedback on how to fix this error message.

    Thanks!


  2. Apr 8th, 2019, 04:35 PM


    #2

    Nish12 is offline

    Thread Starter


    Member


    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    Correction to my code shown above:

    Sub GoalSeek()

    Range(«AC3»).GoalSeek Goal:=Range(«X3»).Value, ChangingCell:=Range(«Y9»)

    End Sub

    When I run this, I now get Run time error ‘1004’:

    Reference isn’t valid.


  3. Apr 9th, 2019, 04:25 AM


    #3

    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    from testing it would appear that the formula is not valid, Y9 must be some part of the calculation

    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete


  4. Apr 9th, 2019, 01:16 PM


    #4

    Nish12 is offline

    Thread Starter


    Member


    Re: Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Referen

    Actually I figured it out. The changing cell had to have a value in it for the macro to run. I just input a dummy amount of $1.00 in there and when I ran the macro it then worked. Leaving the changing cell blank causes the runtime error. Thanks for looking at this!


  • Home
  • VBForums
  • Visual Basic
  • Office Development
  • Goal Seek Macro Giving 400 Error or Compile Error: Invalid or Unqualified Reference


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
  • BB code is On
  • Smilies are On
  • [IMG] code is On
  • [VIDEO] code is On
  • HTML code is Off

Forum Rules


Click Here to Expand Forum to Full Width

Понравилась статья? Поделить с друзьями:
  • Compile error in hidden module this error commonly occurs when code is
  • Compile error in hidden module solvercode excel 2016 как исправить
  • Compile error in hidden module module1
  • Compile error in hidden module ipsolve55
  • Compile error in hidden module excel 2010 как исправить