VBA runtime error

[strongman86]

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:

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.

 

[jwalkerday]

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]

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]

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]

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.

 

[strongman86]

Anyone? Help would be much appreciated.

 

[strongman86]

Guys?

 

[Fluff]

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]

Fluff,
I'm terribly sorry won't happen again. I'm just a poor lad seeking for answers.

 

[strongman86]

Hope anyone can help me with this please. Thanks.