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:

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ᴇʜ

56.7k10 gold badges49 silver badges73 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ᴇʜ

56.7k10 gold badges49 silver badges73 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,6611 gold badge14 silver badges24 bronze badges

3

Источник

  • #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:

  • #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.:)

Источник

Search code, repositories, users, issues, pull requests…

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sign up

Источник

Why am I getting an error on the first line of my macro? ‘Sub Macro5()’? I have never encountered a problem like this before. I feel as though it must be simple.

Sub Macro5()
'
' MacroNew Macro
'
Application.ScreenUpdating = False

 Dim j As Integer
 Dim k As Integer

Worksheets("Resumen").Activate
Columns("Q:V").EntireColumn.Delete

j = 3
Do While Not IsEmpty(Resumen.Cells(j, "A"))
  If Not Resumen.Cells(j, 1).Interior.ColorIndex = xlNone Then
    Resumen.Range(.Cells(j, 1), Cells(j, 2)).Delete Shift:=xlToUp
  End If
Loop
j = j + 1

Application.ScreenUpdating = True

End Sub

Community's user avatar

asked Apr 20, 2017 at 12:03

Byron Pop's user avatar

4

Unless ‘Resumen’ is a codename, you must tell VBE what ‘Resumen’ is. In this case, I declare it as a worksheet object, and set the worksheet object to point to the ‘Resumen’ worksheet within the Workbook running the code. This should now run fine (I fixed your infinite While loop as well).

Sub SomeMacro()
' Name your macros with some kind of informative name

Application.ScreenUpdating = False

' 'k' is never used, delete this.
' Dim k As Integer

Dim Resumen As Worksheet
Set Resumen = ThisWorkbook.Worksheets("Resumen")

' You should avoid activate and select like the plague. Qualify and directly modify instead.
' Worksheets("Resumen").Activate
'
' This reference is unqualified, and operates on the active sheet. Use a qualification instead.
' Columns("Q:V").EntireColumn.Delete
Resumen.Columns("Q:V").EntireColumn.Delete

' Declared j as Long instead of Integer. Otherwise you will eventually hit an overflow error. Always use Long over Integer.
Dim j As Long
j = 3

' If Resumen is a codename (you named the sheet directly using the VBE) then this would work fine
' without first declaring 'Resumen' as a variable, and then setting it properly.
Do While Not IsEmpty(Resumen.Cells(j, "A"))
    If Not Resumen.Cells(j, 1).Interior.ColorIndex = xlNone Then
        Resumen.Range(Resumen.Cells(j, 1), Resumen.Cells(j, 2)).Delete Shift:=xlToUp
    End If

    ' Moved this within the loop since otherwise you will have an infinite loop
    j = j + 1
Loop

' You could also use a with block here instead: 

    ' With Resumen
    '    Do While Not IsEmpty(.Cells(j, "A"))
    '         If Not .Cells(j, 1).Interior.ColorIndex = xlNone Then
    '             .Range(.Cells(j, 1), .Cells(j, 2)).Delete Shift:=xlToUp
    '         End If
    ' 
    '        j = j + 1
    '    Loop
    ' End With

Application.ScreenUpdating = True
End Sub

answered Apr 20, 2017 at 12:16

Brandon Barney's user avatar

Brandon BarneyBrandon Barney

2,3821 gold badge9 silver badges18 bronze badges

The following lines…

Resumen.Range(.Cells(j, 1), Cells(j, 2)).Delete Shift:=xlToUp

Should be

Resumen.Range(Resumen.Cells(j, 1), Resumen.Cells(j, 2)).Delete Shift:=xlToUp

.Cells are used with a WITH and End With Block

answered Apr 20, 2017 at 12:14

Subodh Tiwari sktneer's user avatar

3

Источник

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ᴇʜ

56.4k10 gold badges49 silver badges73 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ᴇʜ

56.4k10 gold badges49 silver badges73 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,6611 gold badge14 silver badges23 bronze badges

3

title keywords f1_keywords ms.prod ms.assetid ms.date ms.localizationpriority

Invalid or unqualified reference

vblr6.chm1011093

vblr6.chm1011093

office

1736db2c-b554-f11d-aa39-9978a0a09db6

06/08/2017

medium

An identifier beginning with a period is valid only within a With block. This error has the following cause and solution:

  • The identifier begins with a period. Complete the qualification of the identifier or remove the period.

For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).

[!includeSupport and feedback]

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

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.

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

Comments

  • 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!

Recents

Источник

Понравилась статья? Поделить с друзьями:

Интересное по теме:

  • Invalid argument supplied for foreach in ошибка
  • Intraos 70 ошибка a02
  • Intex spa e90 ошибка
  • Intex purespa ошибка e98
  • Intex purespa e90 ошибка

    • Добавить комментарий

    ;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: