Excel macro problem

[BoredBloke]

If they are that big you might want to read them into 2 massive arrays so that it speeds up - Excel is painfull when you have to go cell by cell

 

[BoredBloke]

this is probably wrong as i had no data to test it on..................

Sub fill_data()
Dim vArrayS1 As Variant
Dim vArrayS2 As Variant
Dim vArrayS3 As Variant
Dim S1Counter As Long
Dim S2Counter As Long
Dim lgCounter As Long
Dim lgCounter2 As Long
Dim strUsrid As String
Dim strCatid As String
Dim strCatid2 As String
Dim strFunctid As String
Dim lgOCounter As Long

S1Counter = Sheets(1).UsedRange.Rows.Count - 1
S2Counter = Sheets(2).UsedRange.Rows.Count - 1

ReDim vArrayS1(S1Counter, 1)
ReDim vArrayS2(S2Counter, 1)

lgOCounter = 1
'this could be one function but I couldn't be bothered
For lgCounter = 2 To S1Counter
vArrayS1(lgCounter - 2, 0) = Sheets(1).Cells(lgCounter, 1).Value
vArrayS1(lgCounter - 2, 0) = Sheets(1).Cells(lgCounter, 2).Value
Next lgCounter

For lgCounter = 2 To S2Counter
vArrayS2(lgCounter - 2, 0) = Sheets(2).Cells(lgCounter, 1).Value
vArrayS2(lgCounter - 2, 0) = Sheets(2).Cells(lgCounter, 2).Value
Next lgCounter

For lgCounter = 2 To S1Counter
strUsrid = vArrayS1(lgCounter, 0)
strCatid = vArrayS1(lgCounter, 1)

For lgCounter2 = 2 To S2Counter
strCatid2 = vArrayS2(lgCounter2, 0)
strFunctid = vArrayS2(lgCounter2, 1)
If strCatid = strFunctid Then
Sheets(3).Cells(lgOCounter, 1).Value = usrid
Sheets(3).Cells(lgOCounter, 2).Value = catid
Sheets(3).Cells(lgOCounter, 3).Value = functid
lgOCounter = lgOCounter + 1
End If
Next lgCounter2
Next lgCounter
End Sub

 

[DaveB]

Speed isnt so much the problem as much as the fact that the combination of the two sheets will exceed the hard limit on numbers of rows in excel. Hence the multiple sheet idea.

 

[BoredBloke]

OK then....

Sub fill_data()
Dim vArrayS1 As Variant
Dim vArrayS2 As Variant
Dim vArrayS3 As Variant
Dim S1Counter As Long
Dim S2Counter As Long
Dim lgCounter As Long
Dim lgCounter2 As Long
Dim strUsrid As String
Dim strCatid As String
Dim strCatid2 As String
Dim strFunctid As String
Dim lgOCounter As Long
dim intSheetNum as integer

S1Counter = Sheets(1).UsedRange.Rows.Count - 1
S2Counter = Sheets(2).UsedRange.Rows.Count - 1

ReDim vArrayS1(S1Counter, 1)
ReDim vArrayS2(S2Counter, 1)

lgOCounter = 1
'this could be one function but I couldn't be bothered
For lgCounter = 2 To S1Counter
vArrayS1(lgCounter - 2, 0) = Sheets(1).Cells(lgCounter, 1).Value
vArrayS1(lgCounter - 2, 0) = Sheets(1).Cells(lgCounter, 2).Value
Next lgCounter

For lgCounter = 2 To S2Counter
vArrayS2(lgCounter - 2, 0) = Sheets(2).Cells(lgCounter, 1).Value
vArrayS2(lgCounter - 2, 0) = Sheets(2).Cells(lgCounter, 2).Value
Next lgCounter

For lgCounter = 2 To S1Counter
strUsrid = vArrayS1(lgCounter, 0)
strCatid = vArrayS1(lgCounter, 1)

intsheetnum = 3

For lgCounter2 = 2 To S2Counter
strCatid2 = vArrayS2(lgCounter2, 0)
strFunctid = vArrayS2(lgCounter2, 1)
If strCatid = strFunctid Then
Sheets(3).Cells(lgOCounter, 1).Value = usrid
Sheets(3).Cells(lgOCounter, 2).Value = catid
Sheets(3).Cells(lgOCounter, 3).Value = functid

if lgocounter = 65000 then
lgocounter = 1
sheets.add
intsheetnum = intsheetnum+1
else
lgOCounter = lgOCounter + 1
End If
end if
Next lgCounter2
Next lgCounter
End Sub

So when the row count on the output sheet hit 65000 it adds a new sheet ad starts from 1 again

 

[thunderlizard]

You can pretend Excel is Access (well, sort of).

If you create named ranges on each of your 2 data sources, the Excel ODBC provider will see them as data tables. So if you create a DSN for your spreadsheet, you should then be able to query and join the 2 tables.

You can even return the data back into the same spreadsheet: maybe using the MS Query tool that's built into Excel, or maybe using some ADO code sitting behind a user form or other macro.

I was reading a website a couple of weeks ago that explained this really well, but I'm darned if I can find it now.

 

[TheRefactornator]

You can pretend Excel is Access (well, sort of).

If you create named ranges on each of your 2 data sources, the Excel ODBC provider will see them as data tables. So if you create a DSN for your spreadsheet, you should then be able to query and join the 2 tables.

Aye it works I've done it in the past. Not a bad option if you hate VB but are compentent at SQL.

 

[thunderlizard]

found it!

found it!

here: http://www.expresscomputeronline.com/20031020/techspace01.shtml

 

[DaveB]

Got there in the end with some butchery on Pondlifes script and bit's and pieces from everyone else. Thanks for the help everyone. First codeing of any kind i've done in years and first bit of VB ever