VBA runtime error

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97
Lads,

I have issue with my manually entered data on excel spreadsheet misaligning when data is refreshed from MS query e.g row is added or removed by query. I have came across this:
https://www.techsupportforum.com/forums/f57/mis-aligned-data-on-data-refresh-565373.html
I have tried to follow instructions, but when I run VBA I get run-time error '9': Subscript out of range.

In my case:
1) Unique Identifier is in column B (alphanumeric)
2) Yes I have headers
3) Extracted data worksheet name: MdProductDataList
4) Updated data worksheet name: Data
5) Excel is version 2013

Code I'm using:
Code:
Sub test()
Dim cnt, rw As Integer
Dim val As Variant


cnt = Worksheets("Data").Range("B20000").End(xlUp).Row
On Error Resume Next


For rw = cnt To 2 Step -1
val = Empty
val = Application.WorksheetFunction.VLookup(Worksheets("Data").Range("B" & rw).Value, Worksheets("MdProductDataList").Range("B:B"), 1, False)
If val = Empty Then
Worksheets("Data").Rows(rw).Delete
End If
nxt:
Next rw
End Sub

Debug points out this:

cnt = Worksheets("Data").Range("B20000").End(xlUp).Row

How can I fix this? Thanks.
 
Last edited by a moderator:

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

jwalkerday

New Member
Joined
May 1, 2018
Messages
17
That error on that line would indicate that the 'Data' worksheet does not exist.

Can you doublecheck you have created that worksheet before running the code?
 

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97
jwalkerday,

Yes both spreadsheets are on my desktop. Also checked spelling all ok. Note on ''Data'' do I need to point out which tad to use as I have 3 and query is on 3rd tab. On ''MdProductDataList'' only one tab.
 

jwalkerday

New Member
Joined
May 1, 2018
Messages
17
That sounds like your problem.


You need one spreadsheet (called workbook in code) to contain at least Data and MdProductDataList for this code to run successfully.


You can change your code to operate in separate spreadsheets (workbooks) by using the workbook object, something like this if the workbook is open-


Workbooks(“...Accounting Reports.xlsm”).Activate
Sheets(“Data”).Range("B20000").End(xlUp).Row
 

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97

ADVERTISEMENT

jwalkerday,

I've used your suggestion to use one spreadsheet with 2 tabs called ''Data'' & ''MdProductDataList'' and macro now works, but it only works when row is removed by query. When new row is added by query it doesn't seem to do anything. Is there a code i can add for that?
Thanks.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,116
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-pr...ow-but-not-adding-one-after-data-refresh.html

Cross-Posting
While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

strongman86

Board Regular
Joined
Feb 15, 2017
Messages
97
Fluff,
I'm terribly sorry won't happen again. I'm just a poor lad seeking for answers. :rolleyes:
 

Watch MrExcel Video

Forum statistics

Threads
1,108,680
Messages
5,524,249
Members
409,566
Latest member
MickB

This Week's Hot Topics

Top