Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Hou would you optimize this code?

0 views
Skip to first unread message

Hamm...@gmail.com

unread,
Jan 4, 2008, 11:57:37 PM1/4/08
to
Hi,

Here is the background:

I have a rather large database of thousands of rows starting at colum
BA:BH and the list starts at row : 3

BA contains the account#
BD the Date as Date
BG the CrossAttempt as Boolean
BH the Fruit. as String

I want to create a list starting at DA:DH row 8

This list will contain all the entries that match the date between the
FROMDATE (DB:4) and TODATE (DD4)

One Account can have several Fruits but I want it to be listed only
once in one line showing all the fruits list on DD,DE,DF,DG,DH

Thats it.

Here is my code:

StartRow = 3
With Sheets("Tracker")
BigListRowCount = StartRow
SmallListStartRowCount = 8
SmalllistRowCount = SmallListStartRowCount
SmallListAccountNumber = 0
'**************** will need to check that From date is earlier
than To date*****
'Match until end of the database
TotalAttempts = 0
Do While .Range("BD" & BigListRowCount) <> ""
'Found = False
'Match Date Between From and to and Cross = Yes
If .Range("BD" & BigListRowCount).Value
>= .Range("DB4").Value And _
.Range("BD" & BigListRowCount).Value <= .Range("DD4").Value
And _
.Range("BG" & BigListRowCount).Value = True Then
'If list is empty start one
If SmalllistRowCount = SmallListStartRowCount Then
Found = True
.Range("DA" & SmalllistRowCount).Value = .Range("BA" &
BigListRowCount).Value
Select Case .Range("BH" & BigListRowCount).Value
Case "Apple"
TempColumnString = "DE"
Case "Orange"
TempColumnString = "DD"
Case "Grape"
Case "Pear"
TempColumnString = "DF"
Case "cherry"
End Select
.Range(TempColumnString & SmalllistRowCount).Value
= .Range(TempColumnString & SmalllistRowCount).Value + 1
Else
'Verify the list to see if account already exists
TempCountRow = SmalllistRowCount
Found = False
Do While TempCountRow >= SmallListStartRowCount
'if Found account in the list, add to the Policy type
value
If .Range("DA" & TempCountRow).Value = .Range("BA" &
BigListRowCount).Value Then
Select Case .Range("BH" & BigListRowCount).Value
Case "Apple"
TempColumnString = "DE"
Case "Orange"
TempColumnString = "DD"
Case "Grape"
Case "Pear"
TempColumnString = "DF"
Case "cherry
.Range(TempColumnString & TempCountRow).Value
= .Range(TempColumnString & TempCountRow).Value + 1
Found = True
Exit Do
Else
TempCountRow = TempCountRow - 1
End If

Loop
'No account found in the list
If Found = False Then
.Range("DA" & SmalllistRowCount).Value = .Range("BA" &
BigListRowCount).Value
Select Case .Range("BH" & BigListRowCount).Value
Case "Apple"
TempColumnString = "DE"
Case "Orange"
TempColumnString = "DD"
Case "Grape"
Case "Pear"
TempColumnString = "DF"
Case "cherry
.Range(TempColumnString & SmalllistRowCount).Value
= .Range(TempColumnString & SmalllistRowCount).Value + 1
Found = True
End If
End If
If Found = True Then
TotalAttempts = TotalAttempts + 1
SmalllistRowCount = SmalllistRowCount + 1
End If
End If
BigListRowCount = BigListRowCount + 1
Loop
End With

=====
It works, but I am no expert in coding and I was wondering if it could
be optimized to be faster/smaller?
Any sugestions are welcome.
Thanks

Roger Govier

unread,
Jan 5, 2008, 6:51:04 AM1/5/08
to
Hi

Rather than coding, you could use a Pivot Table.

Place cursor in BA3>data>Pivot Table>Finish
On the new sheet with the PT Skeleton,
Drag Date to the Row area
Drag Account to the row area
Drag Cross Attempt to the Page area
Drag Fruit to the Column Area
Drag Fruit again to the data area

Use the dropdown on Page field to select Yes
Use dropdown on date to deselect All, and select the dates that you want

You will see numbers against each account code, where any fruits are
involved, the number being the count of those fruits.

If you did need the Text of the fruit to be extracted as you do in your
coding method, then some simple IF formulae could be used to write the names
out to another part of the sheet, where there is a number in the relevant
cell of the PT.

--

Regards
Roger Govier

<Hamm...@gmail.com> wrote in message
news:202d27c5-2566-457a...@e25g2000prg.googlegroups.com...

Hamm...@gmail.com

unread,
Jan 9, 2008, 11:57:24 PM1/9/08
to
Thanks for the sugestion to use Pivot Table.
It is a rather interesting table wizard.


On Jan 5, 7:51 am, "Roger Govier" <roger@technology4unospamdotcodotuk>
wrote:

Hamm...@gmail.com

unread,
Jan 10, 2008, 12:02:49 AM1/10/08
to
I guess my code is okay seeing that nobody has any sugestions to
simplify it. :)
This code works fine, with one exception.
I wanted to get the new list on a different sheet and I dont know how
I can use two different sheets inside the With Sheets loop.

I thought that maybe instead of ".Range(TempColumnString &


TempCountRow).Value
= .Range(TempColumnString & TempCountRow).Value + 1"

I could use : "Sheets(NewSheet).Range(TempColumnString &
TempCountRow).Value
= Sheets(NewSheet).Range(TempColumnString & TempCountRow).Value + 1"
to redirect to the new sheet but I get a Ref error.
Any sugestions?
Thanks


On Jan 5, 12:57 am, "Hammer...@gmail.com" <Hammer...@gmail.com> wrote:
> Hi,
>
> Here is the background:
>
> I have a rather large database of thousands of rows starting at colum
> BA:BH and the list starts at row : 3
>
> BA contains the account#
> BD the Date as Date
> BG the CrossAttempt as Boolean
> BH the Fruit. as String
>

>======
Code deleted

Roger Govier

unread,
Jan 10, 2008, 3:36:16 AM1/10/08
to
Hi

I would set references to the 2 sheets at the beginning of your routine.

Dim wss as Worksheet, wsd as Worksheet
(I tend to use wss as the Source sheet and wsd as the Destination sheet)
Set wss = ThisWorkbook.Sheets(("Sheet1")
Set wsd = ThisWorkbook.Sheets("Sheet2")
(Change sheet names to suit your example)

Then refer explicitly to which sheet you want the action to take place

wsd..Range(TempColumnString & SmalllistRowCount).Value

--

Regards
Roger Govier

<Hamm...@gmail.com> wrote in message
news:e39d0660-a9b6-46f0...@v67g2000hse.googlegroups.com...

Roger Govier

unread,
Jan 10, 2008, 7:33:10 AM1/10/08
to
> wsd..Range(TempColumnString & SmalllistRowCount).Value

Apologies, that should of course not have had two periods.
Corrected version

wsd.Range(TempColumnString & SmalllistRowCount).Value

--

Regards
Roger Govier

"Roger Govier" <roger@technology4unospamdotcodotuk> wrote in message
news:1878CE96-860E-4B9D...@microsoft.com...

Susan

unread,
Jan 10, 2008, 8:34:57 AM1/10/08
to
hiya! i don't know about whether or not it's faster, but when i have
an area of coding that keeps repeating, i make it a separate code.
then you only have to have it written out once, and then also if you
make changes to it, you don't have to remember to change it in every
instance...........

like this:

-----------------------------------

call Select_my_Case

End Select
.Range(TempColumnString & SmalllistRowCount).Value
= .Range(TempColumnString & SmalllistRowCount).Value + 1
Else
'Verify the list to see if account already exists
TempCountRow = SmalllistRowCount
Found = False
Do While TempCountRow >= SmallListStartRowCount
'if Found account in the list, add to the Policy type
value
If .Range("DA" & TempCountRow).Value = .Range("BA" &
BigListRowCount).Value Then
Select Case .Range("BH" & BigListRowCount).Value

call Select_my_Case

.Range(TempColumnString & TempCountRow).Value
= .Range(TempColumnString & TempCountRow).Value + 1

Found = True
Exit Do
Else
TempCountRow = TempCountRow - 1
End If

Loop
'No account found in the list
If Found = False Then
.Range("DA" & SmalllistRowCount).Value = .Range("BA"
&
BigListRowCount).Value
Select Case .Range("BH" & BigListRowCount).Value

call Select_my_Case

.Range(TempColumnString & SmalllistRowCount).Value
= .Range(TempColumnString & SmalllistRowCount).Value + 1
Found = True
End If
End If
If Found = True Then
TotalAttempts = TotalAttempts + 1
SmalllistRowCount = SmalllistRowCount + 1
End If
End If
BigListRowCount = BigListRowCount + 1
Loop
End With


Private Sub Select_my_Case()


Case "Apple"
TempColumnString = "DE"
Case "Orange"
TempColumnString = "DD"
Case "Grape"
Case "Pear"
TempColumnString = "DF"
Case "cherry

End Sub
-----------------------------------------------
i might have messed up your coding somewhat, but you get the idea.
:)
susan


On Jan 10, 12:02 am, "Hammer...@gmail.com" <Hammer...@gmail.com>
wrote:

> > Thanks- Hide quoted text -
>
> - Show quoted text -

Hamm...@gmail.com

unread,
Jan 15, 2008, 5:45:48 PM1/15/08
to
I need help with this portion of the code:

'Verify the list to see if account already exists
TempCountRow = SmalllistRowCount
Found = False
Do While TempCountRow >= SmallListStartRowCount
'if Found account in the list, add to the Policy type
value
If .Range("DA" & TempCountRow).Value = .Range("BA" &
BigListRowCount).Value Then
Select Case .Range("BH" & BigListRowCount).Value

Case "Apple"
TempColumnString = "DE"
Case "Orange"
TempColumnString = "DD"
Case "Grape"
Case "Pear"
TempColumnString = "DF"
Case "cherry

.Range(TempColumnString & TempCountRow).Value
= .Range(TempColumnString & TempCountRow).Value + 1
Found = True
Exit Do
Else
TempCountRow = TempCountRow - 1
End If

Loop
====
It works, but it seems to be very slow. What it does is it goes thru
the new list to verify that the account# is not already there.
This being Excel, isnt there just a command like
=MATCH(SmalllistRowStart,SmalllistRowCount, .Range("DA" &
TempCountRow).Value, .Range("BA" &
BigListRowCount).Value) where match(beginning of range, end of range,
value of the range to compare, to what?)

I need a faster routine than this, otherwise with several thousand of
entries it will take minutes to compute. :)
Thanks

Hamm...@gmail.com

unread,
Jan 17, 2008, 11:26:23 AM1/17/08
to
Any ideas please?

I need to speed this quite a bit.
Thanks

0 new messages