VBA stopped working, I made no changes...

wallydonkey · Feb 24th 2021

Hello,

So I am very much a VBA noob, I cobbled something together from snippets and tutorials online and it worked. Very simply it does a lookup in a table to find a value that matches my input, and then copies that row into another sheet. It worked perfectly up until this morning when it suddenly started throwing match errors. I haven't made any changes to it so I am very confused. The code is as follows:

Code

Sub CopyYes()
Dim c As Range
Dim j As Integer
Dim Source As Worksheet
Dim Target As Worksheet

lookupvalue = ActiveWorkbook.Sheets("ModifyAsset").Range("L5").Value

' Change worksheet designations as needed
Set Source = ActiveWorkbook.Worksheets("AssetRegister")
Set Target = ActiveWorkbook.Worksheets("ModifyAssetDump")

j = 1     ' Start copying to row 1 in target sheet#
For Each c In Source.Range("AX1:AX10000")
If c = lookupvalue Then  ##### THIS IS THE LINE HIGHLIGHTED WITH A MATCH ERROR ####
Source.Rows(c.Row).Copy Target.Rows(j)
j = j + 1
End If
Next c

Display More

If anyone has any ideas on what might have gone wrong here I would really appreciate it! Happy to provide more information etc. as needed, wasn't sure how much info to start off with here.

**Carim** · Feb 24th 2021

Hello and Welcome to the Forum

What is precisely your variable lookupvalue ...?

Do you know why you are using ActiveWorkbook ... when you are only using one single workbook ...?

AndrewMB · Feb 24th 2021

Easiest way to find out, when it stops on the error, in the Immediate pane type ?c.address and press Return.

This will tell you which cell it is failing on.

Look at this cell and see if there is anything there that might be causing the problem.

**royUK** · Feb 24th 2021

An example workbook would help, I'm sure there is a better way to do this

**rory** · Feb 24th 2021

The most likely cause is actual errors in the cells (eg #N/A, #NAME?, #VALUE! etc). Not much else would give you a Type Mismatch on that line.

wallydonkey · Feb 25th 2021

Thank you all for your input, apologies it took a while to get back to you but this was all done on my work laptop and I wasn't back in until this morning. Using your advice I have now found the issue, someone had slipped in an invalid user number and it had thrown an #N/A error which stopped the whole thing working. I've thrown an IF into that lookup that will filter out potential errors now and that has fixed the issue. Whilst I'm sure the whole thing could be way way smoother it does more or less work at the moment and unfortunately due to it being a police force product I'm unable to upload the file for any more detailed assistance. However it is up and running again so thank you all very much!

VBA stopped working, I made no changes...

Participate now!

Share

Tags