Search:

# trouble with IF/OR, Left and a simple loop

Asked By: Ayden    Date: Feb 20    Category: MS Office    Views: 1682

I'm trying to write a code which will group and sum up a number of
acounts (sheet T_B) into fewer categories (Sheet BS) based on the
account number (account number-column A, account amount-column E). I
must be doing smth wrong since the variable mysum adds all the
accounts despite the "left" test. Also I'm not sure on the
combination of IF and OR if it works like that - I want to evaluate
if a string begins with say 50, 511, 531. Anything better than "left"
to see if a string begins with some characters?

Here's the code:

Dim mysum As Variant
Application.ScreenUpdating = False
Sheets("T_B").Select

For Each c In Range("A2:A13")
c.Select
If Left(c, 2) = 50 Or 511 Or 512 Or 531 Then _
mysum = mysum + ActiveCell.Offset(0, 4).Value
Next c
Sheets("BS").Select
[b2].Value = mysum

Sheets("T_B").Select

mysum = 0
For Each c In Range("A2:A13")
c.Select
If Left(c, 2) = 41 Then _
mysum = mysum + ActiveCell.Offset(0, 4).Value
Next c
Sheets("BS").Select
[b3].Value = mysum

Sheets("T_B").Select

mysum = 0
For Each c In Range("A2:A13")
c.Select
If Left(c, 3) = 101 Or 108 Or 104 Then _
mysum = mysum + ActiveCell.Offset(0, 4).Value
Next c
Sheets("BS").Select
[b30].Value = -mysum

Application.ScreenUpdating = True

End Sub

Share:

OK, I can see a few things right away.
the "or" construct is expecting to compare two expressions that RESULT in true
or false.
So, the "or 511" is meaningless, or at least not what you're looking for.
Next, I wouldn't recommend "selecting" each cell. It causes WAY too much screen
updating.
What I would do is utilize the Value property as in c.Value
then, the Left function is going to return a character string, but you're
comparing it to a
numeric value, which can be unpredictable.
What you have to do is write  it like:
If ((Left(c.Value, 2) = "50") _
Or (Left(c.Value,2) = "511") _
Or (Left(c.Value,2) = "512") _
Or (Left(c.Value,2) = "531")) Then
I haven't had time to test  it, I'm kind-of in a rush...
hope this helps.

Put the breaks on! Am I missing something?
The function Left(c.Value, 2) returns a 2 character string! ...so how are we
going to evaluate a 3 character string? What I would like to see is your actual
'string' examples to better determine the decision logic. Seeing the actual
examples may shed some light on the best method for solving your specific case;

I noticed that to but didn't add left(c,3) because it wasn't working
anyway.

Here is the example:
Acc# Amount
1011 0
1012 -12460
1015 0
108 0
1041 -2590
411.gala 50000
413 0
416 23687
418-bold 18700
419 -6585
5121orh 23500
5311.hd 2350

many thanks, it works  perfect...........

To mimic your code  sample, something like this :

Dim rngSource As Range
Dim avarAccounts() As Variant
Dim varSum As Variant

Dim strValue As String
Dim rngCell As Range
Dim varInc As Variant

' Initialization
Set rngSource = ThisWorkbook.Sheets(1).Range("A2:A13")
avarAccounts = Array("50", "511", "512")

' Sum
varSum = 0
For Each rngCell In rngSource
strValue = rngCell.Value2
For Each varInc In avarAccounts
If Left\$(strValue, Len(varInc)) = varInc Then
varSum = varSum + rngCell.Offset(0, 4).Value
Exit For
End If
Next varInc
Next rngCell

' Save Result
' ...

A better alternative would be to let Excel do the calculation for you

Dim strAccounts As String
Dim strAmounts As String
Dim avarAccounts() As Variant
Dim varSum As Variant

Dim varInc As Variant

' Initialization
strAccounts = "sheet1!\$A\$2:\$A\$13"
strAmounts = "sheet1!\$E\$2:\$E\$13"
avarAccounts = Array("50", "511", "512")

' Compute Sum
varSum = 0
For Each varInc In avarAccounts
varSum = varSum + Application.Evaluate("=SUM((LEFT(" & strAccounts & ","
& Len(varInc) & ")=" & """" & varInc & """)*(" & strAmounts & "))")
Next varInc

' Save Result
' ...

1st solution works  perfect! (the second gives a type mismatch error)
many thanks

Looks like extracting the 'numeric' part of the Acct# is what you need:

Sub FindNumericPartOfAcctNumber()

StringToEvaluate = ActiveCell.Value
StrLength = Len(StringToEvaluate)
LeftNumeric = 0

For ct = 1 To StrLength
vCharacter = Mid(StringToEvaluate, ct, 1)
Select Case vCharacter
Case 0 To 9
LeftNumeric = LeftNumeric + 1
Case Else
ct = StrLength
End Select
Next ct
NumericPartOfAcctNumber = Left(StringToEvaluate, LeftNumeric)

End Sub

This simply looks at the selected cell (active cell) & starts from left  to right
to find where the 1st non numeric position is located within the Acct# field.
The next step is to set up the loop  structure to run down the Acct# column. Then
add the decision logic (Select Case may be in order)

select case NumericPartOfAcctNumber
case 50
'place what you want to do for Acct# 50 here
case 511
'place what you want to do for Acct# 511 here
case 512
'place what you want to do for Acct# 512 here
case 531
'place what you want to do for Acct# 531 here
case else
'place what you want to do for Acct# NOT in the above list here
end select

If the Value part (on the left  side) is you are looking for, you could simplify
by using VAL function.
Val(343.HP.345)=343

The line item "416 23687" which is "416" [space] 23687" translates to 41623687
using the =val("416 23687") function. Also the following also returns an
unexpected result:
=val("416 0.23687"). This returns the value of 4160.23687 instead of the
expected Acct#416 with Count Qty of 0.23687.

Next question:
Is the Acct# a separate column 'field' in the data? If so, the Val(Acct#) will
work. Otherwise the strategy to 'scan' the data item content for a non-numeric
character still applies!

Acct# is a separate column but I just realized that because the data
is normally exported as text by a custom accounting application it may
noticed) the FindNumericPart Sub does not work (Ex. data is "acct
411.Domestic Customers" - in which case I need to extract 411). Can
you provide a fix?

My solution was based  on the example data set given,Please include additional
examples to survey. Thanks

see if this helps:

Sub FindNumericPartOfAcctNumber()

StringToEvaluate = ActiveCell.Value

StringToEvaluate=LTrim(StringToEvaluate)

StringToEvaluate=RTrim(StringToEvaluate)

StrLength = Len(StringToEvaluate)
LeftNumeric = 0

For ct = 1 To StrLength
vCharacter = Mid(StringToEvaluate, ct, 1)
Select Case vCharacter
Case 0 To 9
LeftNumeric = LeftNumeric + 1
Case Else
ct = StrLength
End Select
Next ct
NumericPartOfAcctNumber = Left(StringToEvaluate, LeftNumeric)

End Sub

it does not return the numeric part if the data begins with a
non-numeric character.

anyway, this is what I am trying to automate: there is a series of
accounting numbers (ex. 1011, 411, 5121, 531) which are compulsory, so
these will be in the data for sure, but the problem is that additional
characters may precede or follow them when exported by the accounting
application, for ex:
Column1(Acct.)/Column2(\$)
Acct. 1011 - Social Capital/200.000
1. 411.1 - Domestic customers/15.600
2. 411.2 - Overseas customers/2.500
5121 local currency/6.500
5121 foreign currency/10.200
# 531-Cash on hand/500

I want to group  these fields as follows: the amounts corresponding to
every account number  (the compulsory one) that begins with 51 and 53
should be added together in a single row on a different sheet  (to get
say Cash available/17.200; the same for accounts beginning with 411
and so on; this gets complicated when those numbers are attached to
other characters in the same field.

Is it possible to have such a general procedure? maybe to have a loop
inside a loop: first to compare column one fields to the array of all
the compulsory accounts (numbers only) and then proceed with a second
loop to see if they begin say with 411?

The extraction can be made as simple  or as complicated as you wish. That is not
the problem.

The problem appears to be one of defining rules which will RELIABLY find the
account number  in the extracts.

If you can define the rules, they can be coded.

It's unusual for an accounting application to export information in such an
unstructured form. In particular, I would be extremely surprised if the export
can't be redefined so as to put the account numbers into a field of their own.
After all, they will be being held separately inside the accounting application.

I would recommend going back to the people doing the extract, and get them to
structure it more appropriately.

I am in alignment with Dave on the restructure part! Looks like the Acct#Name(a
'descriptive' text string  for the Acct#) is being exported/concatinated with the
Acct# (numeric) field. You may not always have some say regarding the data file
received, but if you are providing a 'service' by crunching the data; there may
be leverage opportunity for defining the data input requirements from your
perspective. There is a higher marging of erroneous outputs as the incoming data
sets are so varied; your chance for 'catching' 100% of the 'exceptions' becomes
futile! Untill that has the option for bringing the issue(s) to the negotiating
table, we (you) are stuck with what you got!
...and then there is this team of peripheral grey matter to lean on.

point taken. unfortunately sometimes the authors of the accounting
application are not reachable and users don't know how to or cannot
make those changes in the data.

Your end result IS do-able. We just have to see more examples of the actual
Acct# to evaluate the 'correctly' segregate the list as expected.
I would like to see an example of just the Acct# column for a complete data set.
Any possibilites delivering for that?

The fact is that there are several accounting applications (each group
company has a different system) which export data in different formats
and I am looking for an automation general enough so that I will not
have errors when running the code  for each of them (the format is
consistent within each application though).

With respect to the data format the most common are, say for the first
account from the list below: "1011 account name","1011.number " and
"1011.txt" but I also found "txt 1011".

Below are all the account numbers which are compulsory and will be
found as such, uninterrupted, in the data exported, but more likely
will be followed by some other characters (or preceded) as explained
(they are not compulsory in the sense that all of them will be in the
exported data - depending on their activity, some companies do not use
certain categories from the list below - but if you have transactions
which falls into one account, then its number  must be shown as a
single block, even if accompanied by other information.

So the first challenge would be to separate those numbers from the
rest of characters. The second challenge, already solved thanks to
your help, was to regroup them based  on whether they begin or not with
some specific digits (the first digit indicates a class, the second a
category within the class and the third a group  within the category).

Here are the account numbers:

Acc#
1011
1012
1015
108
1041
1042
1061
1063
1068
107
111
112
118
119
105
121
129
141
1511
1512
1513
1514
1518
161
1621
1622
1623
1624
1625
1626
1627
166
167
1681
1682
1686
1687
169
201
203
205
207
208
211
2111
2112
212
2121
2122
2123
2124
2125
2126
2127
2128
230
231
261
262
263
2671
2672
2673
2677
2678
269
280
2801
2803
2805
2807
2808
281
2810
2811
2812
2813
2814
2815
2816
2817
2818
290
291
293
296
300
3011
3012
3013
3014
3015
3016
3018
308
321
322
323
328
331
332
341
345
346
348
351
352
354
356
357
358
361
368
371
378
381
388
390
391
392
393
394
395
396
397
398
401
403
404
405
408
409
411
413
416
418
419
421
422
423
424
425
426
427
4281
4282
429
4311
4312
4371
4372
4381
4382
441
4423
4424
4426
4427
4428
444
445
446
447
4481
4482
4483
4484
4485
4511
4518
4551
4558
456
457
4581
4582
459
461
462
463
471
472
473
476
477
481
482
491
495
496
502
503
505
506
5081
5088
509
5112
5113
5114
5121
5122
5124
5125
5126
5129
5186
5187
5191
5192
5193
5194
5195
5196
5197
5198
5311
5314
5321
5322
5323
5328
5411
5412
542
581
590
600
6011
6012
6013
6014
6015
6016
6018
602
603
604
605
606
607
608
611
612
613
614
621
622
623
624
625
626
627
628
631
635
641
642
6451
6452
6458
651
654
658
663
664
665
666
667
668
6711
6712
6714
6718
6721
6728
681
6811
6812
6813
6814
686
6862
6863
6868
687
6871
6872
6873
6874
691
701
702
703
704
705
706
707
708
711
721
722
741
751
754
758
761
762
763
764
765
766
767
768
7711
7718
7721
7727
7728
781
7812
7813
7814
786
7862
7863
787
7872
7873
7874

OK, I think I have the basic logic for the Acct number  issue!!
See if this helps....

Sub TestAcctNumberCell()
vAcctNew = vAcctOriginal
vLength = Len(vAcctOriginal)

For vChar = 1 To vLength
vCharPosition = Mid(vAcctNew, vChar, 1)
Select Case vCharPosition
Case "."
'leave Char position alone!
Case 0 To 9
'leave Char position alone!
Case Else
'replace any non numeric value with a 'blank'
Mid(vAcctNew, vChar, 1) = " "
End Select
Next vChar

SplitAcct = Split(vAcctNew, ".")
For i = 0 To UBound(SplitAcct)
Range(vCellAddress).Offset(0, 4 + i).Value = SplitAcct(i)
Next i
End Sub

Copy some example AcctNumbers with mixed text, periods & numbers.
Then click to select an AcctNumber to test  with the Code above.
The results will be written 3 cells to the right of the selected cell.
The 3rd cell shows the original AcctNumber.
The 4th cell will show the prefix or 'left' side of a decimal AcctNumber.
The 5th cell will show the suffix or 'right' side of a decimal AcctNumber.

If this solves your case, this can be set up to run against the whole AcctNumber
& Amount data columns by loading the data into array structures (very fast!).

Anyway, see if this fits the bill.

Awesome! it works  very well, thank you very much for your help.

so many great solutions, thank you all!

one step further: after aggregating the data I was looking for a nice
way to show what it is made of - similar with the double click on a
cell in a pivot table. so I found this code  for a cell double click event:

Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Range("b2")) Is Nothing Then

..and here i would like to do what the pivot-table does when you
double-click a cell, i.e. to copy only the rows used in the
computation of that cell (I also recorded a macro while
double-clicking the pivot table but..the part which filters and copies
the rows is missing from the code).

any ideas how to do this?

Didn't find what you were looking for? Find more on trouble with IF/OR, Left and a simple loop Or get search suggestion and latest updates.