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 «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ᴇʜ
55.8k9 gold badges49 silver badges72 bronze badges
asked Sep 8, 2017 at 12:35
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ᴇʜ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 SportelRik Sportel
2,6511 gold badge13 silver badges23 bronze badges
3
snipe, спасибо, j исправил.
про объявление переменных учту, но сейчас главное что бы программа работала корректно, оформлять код буду позже.
Так, всем интересующимся рассказываю, как должен работать макрос:
действующие столбцы: B, G, H.
просматриваем столбец B на одинаковые значения (они, повторяясь, сначала увеличиваются, потом уменьшаются).
считаем среднее арифметическое соответствующих значений в столбца G (у строк которых значение столбца B одинаковое). Далее вывод результата среднего арифметического в столбец H.
после нескольких дней одиночного мозгового штурма у меня получился вот такой код…
Visual Basic | ||
|
объясняю. использовал в качестве фильтра (что б не повторялись значения столбца 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.
-
11-09-2015, 04:33 AM
#1
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.
-
11-09-2015, 05:24 AM
#2
Re: getting compile error invalid or unqualified reference
-
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 WithRichard 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.
-
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.
-
11-09-2015, 08:36 AM
#5
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
|
|