value points to cell C1 3

[natedeia]

************this is before
Option Explicit
Const THEFT_THRESHHOLD = 0.75
Const SEARCH_COLUMN = "A:A"
*************this is after, does not work
Option Explicit
Const THEFT_THRESHHOLD = Worksheets("Sheet1 (2)").Cells(1, 3).Value
Const SEARCH_COLUMN = "A:A"

Zathras helped me with this one but need to change just a tad. I would like have cell C1 hold the value in place of where the 0.75 is in the above one. Can someone tell me what is wrong with second example.

 

[xlbo]

all these programmers (like you guys) make too much

Ha....ha ha ha.....ha ha ha ha ha ha ha ha. Do you really think that someone who programs in VBA gets paid a lot ??

Oracle DBA is a completely different to coding VBA - they may well be on a reasonable amount of money...but programmers...sorry dude but you've got entirely the wrong idea there

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[xlbo]

Something you may want to look into if you don't want to go the Access route is array comparisons - they are quicker than a range comparison and can be useful on large sets of data in excel. I posted something similar to the following code some while ago - might be worth having a look and integrating it with Zathras' function - instead of comparing 2 ranges digit by digit, it would need to compare 2 array elements:

Sub GetDiffs()
Dim lRow As Long, refArr As Variant
Dim refSht As Worksheet, CurrNum as long
Const Col = "J"
Set refSht = activesheet

Application.ScreenUpdating = False

lRowRef = refSht.UsedRange.Rows.Count
[COLOR=green]'Assign a range of data to the array[/color]
refArr = refSht.Range(Cells(1, Col), Cells(lRow, Col))
[COLOR=green]'Loop through array[/color]
For a = 1 To UBound(refArr)
 CurrNum = refArr(a)    
  For b = 1 To UBound(refArr)
   [COLOR=green]'Perform Zathras' % match here, comparing CurrNum to each element of the array[/color]
   [COLOR=green]'Write to another sheet to store result[/color]
  Next b
Next a
End Sub

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[natedeia]

thanks xlbo, i will look at that closely tonight for sure.

**well it is just that every programmer i have known or met has been very close to 6 digits. like you guys, that are quick on this stuff most likely have a very good reasoning and quick thought process, so typically from people i know or met will always excel in other areas as well. i am not the best with mathmatics, but i think that i can design well, have done several compay intranet sites and set up online websites for a few companines i word with or for.
if not making big bucks most likely have the potential.

 

[natedeia]

someone here just started running it on 64,000 rows! i will let you know how it went............next year!

 

[xlbo]

ROFLMAO (ps - divide the lowest 6 digit number you can get by 4 and you'll be about right !)

Rgds, Geoff

"Three things are certain: Death, taxes and lost data. Guess which has occurred"

Please read FAQ222-2244 before you ask a question

 

[Zathras]

Ok. With that much data, a different algorithm is required.

First, let's be clear about the specs.

1. You have a spreadsheet with up to 65,000 9-digit numbers in column "A"

2. You want to find pairs of those numbers who have either 7 or 8 digits that match (same number, same position) and differ in 1 or 2 positions at most.
For example, 931-03-2884 and 971-03-2884 differ in the 2nd position, and 850-90-7223 and 853-90-7923 which differ in the 3rd and 7th positions.

3. You want to run on a 2.6 Mhz machine in under 10 minutes.

If those are the correct specs, I have worked out macros to do exactly that. On a 1.4 Mhz machine, 4,000 numbers crunch in about 20 seconds. 16,000 numbers crunch in about 90 seconds -- just a little worse than proportional as the number of numbers increase. (4x as many numbers, 4.5x the time.) And it's quite sensitive to CPU speed. (4,000 numbers crunch in 3 minutes on my 450 Khz machine.)

I'm still polishing the code, but if those are your specs I can post sometime after the weekend.

If you alter the specs to find pairs of numbers that have exactly 8 digits in common and only 1 digit different, that version would process in about one-third of the time.