Find Function in a range does not work

[RAFA_NADAL]

Hi everyone,
I am trying to code a macro to transfer data from column Part Number of sheet 1 to row with similar Key at sheet 2:
1.png
2.png
My code is:
1) I clear the old data at sheet 2 and return to sheet 1.
2) I create 2 coulmns and add data to them to use latter.
3) I trim the key column and apply autofilter.
4) I copy key column to AK column and remove duplicate to use as variable for autofilter latter.
5) I use data of AK column to apply to autofilter and use Find function to get the required data and add to sheet 2.

My problem is that the Find function does not return the cell which contains the value and the code return Runtime Error 91: Object variable or with block variable not set.

Please check and help me to correct the fault.

[hernantorres23]

If you can share original source of data, I think that Power Query can resolve this.

But, follow your code, will find your first error (show below)...
Set cell = Cells(1 + t, 37)
'MsgBox cell.Address
firstCellAddress = cell

I changed the Set, to convert it, in a Range.
Although, I really don't understand what try to do, verify this error, before continuing

[LJMetzger]

Hi Rafa - love your forehand.

Nice code. You obviously have programming experience, but the Excel MacroRecorder has led you astray on how VBA can be written more efficiently.
'Select' causes code to run slower, and is not needed a great percentage of the time.

I was able to get your code to work with the small change in red below. Please NOTE that your sort does not work, because there is some code missing. However, when the proper sort code is added and the sort works, the workbook finishes with rows in the wrong place.

See the attached file that contains several different versions of Sub Main().

Please Login or Register  to view this content.

To correct the sorting problem the following code has to be added:

Please Login or Register  to view this content.

The following suggestions may help you in the future:
a. Use 'Option Explicit'
To prevent typos from ruining days and weeks of work 'Option Explicit' is NEEDED at the top of each code module. This prevents errors caused by missspellings and FORCES every variable to be DECLARED (e.g. Dim i as Integer). https://www.excel-easy.com/vba/examp...-explicit.html
b. Use 'If then else' rather than 'goto abc' in most cases
c. Debug.Print (rather than MsgBox) outputs to the Immediate Window (Ctrl G in the debugger)
NOTE: Debug.Assert.False will act as a permanent breakpoint (often useful after a print)
d. Use Column Letters rather that Column Numbers (e.g. Cells(1, "D") is easier to read that Cells(1, 4) )
e. Qualify range to identify the worksheet (e.g. ws.Cells(1,4) rather than Cells(1,4) )
f. The .find routine can be hard to debug if all the parameters are not used, because the parameters are 'Sticky' if not explicitly named. See https://docs.microsoft.com/en-us/off...cel.range.find

Please Login or Register  to view this content.

g. Use of 'Select' slows down the code - especially inside of loops

Benchmark Times on my computer:
a. Original = 4.5 seconds
b. MainOriginalModified() average time = 3.0 seconds
c. MainOriginalOptimized() average time = 1.15 seconds
d. MainRewrite1() average time = 1.10 seconds
e. MainRewrite2() average time = 0.85 seconds

Changes:
MainOriginalModified() - Added/removed 'Task Card'/Dates in 'A' & 'B' white cells (instead of adding/deleting 2 columns). Implemented Sort. Added Temporary Use of Column 'Z' to contain original row numbers so original 'Task Card' row numbers can be restored.
MainOriginalOptimized() - Previous changes Plus - Removed Select where applicable. Added use of Worksheet Objects.
MainRewrite1() - Previous changes Plus - Replaced use of Column 'AK' with a 'Scripting Dictionary'. An Excel Scripting Dictionary is very good at identifying (and counting) unique items in a list.
Reference: http://www.experts-exchange.com/Soft...ss-in-VBA.html
Reference: http://www.snb-vba.eu/VBA_Dictionary_en.html
MainRewrite1() - Previous changes Plus - Replaced use of 'AutoFilter' with putting results in an array in memory and then sorting by date.

Lewis

[LJMetzger]

Continuation of previous post:

Rewrite 1 code:

Please Login or Register  to view this content.

Lewis

[LJMetzger]

Continuation of previous post:

Rewrite2() Code:

Please Login or Register  to view this content.

Lewis