Reply to: Excel macro problem

[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

[thunderlizard]

found it!

here: http://www.expresscomputeronline.com...hspace01.shtml

[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]

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.

[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

[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]

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

[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

[DaveB]

Pondlife, that would be spot on but for the fact that the two sheets contain 12746 and 15115 records respectivly. If I restict the number of rows read, rather than letting it try and go to the end it works just fine.

With a bit of jiggery pokery I think I can make it work by doing it by user ID from the 1st sheet and searching for the matching categories in the second sheet then creating a new sheet for each user ID ( 207 in total ). Back to the VB books.

[Pondlife]

If the used rows in sheet1 x used rows in sheet2 < 65K then populate sheet 3 with the following and filter on col1

Sub fill_data()
lastrec = 1
i = 2
 Do Until i = Sheet1.UsedRange.Rows.Count + 1
  usrid = Sheet1.Cells(i, 1).Value
  catid = Sheet1.Cells(i, 2).Value
   j = 2
   Do Until j = Sheet2.UsedRange.Rows.Count + 1
    catid2 = Sheet2.Cells(j, 1).Value
    functid = Sheet2.Cells(j, 2).Value
    If catid = catid2 And functid <> "" Then
     Sheet3.Cells(lastrec, 1).Value = usrid
     Sheet3.Cells(lastrec, 2).Value = catid
     Sheet3.Cells(lastrec, 3).Value = functid
     lastrec = lastrec + 1
    End If
   j = j + 1
   
   Loop
   
   i = i + 1
  Loop
End Sub

[EternalOptimist]

build a third column

=b1 & a1

this will concatenate the two fields then sort.
all your func1 will be grouped together


a pooey solution I agree

[MrRobin]

Your client doesn't have Access licences??!! Are they using Office Home edition or something??

Anyway, if TonyEnglish's solution isn;t any good for you, I think this might actually be possible without using any VB, just some clever functions like OFFSET and FIND etc... So long as your data is sorted in order

[DaveB]

Thanks for the replies guys, Access isn't an option unfortunately. Client doesnt want to buy a license just for this job

Tony, I've PM'd you.

[BoredBloke]

are you trying to have 2 list boxes - the contents of the second depending on the value in the first? If so I have something which you might find useful - in Excel

Send me a pm with youe mail in it and I'll send it you

I nicked it from http://www.brainbell.com/tutorials/m...other_List.htm

as I had a similar problem a while ago. The example in the page above doesn't quite work but I managed to fix it.

[Sockpuppet]

Yes, its possible but maybe not filter as AFAIK that allows 2 criteria.

If it was me I'd upload it to Access and SQL it.