View Full Version : Can not understand what it means!
krishnak
03-21-2008, 04:13 PM
Hi All,
I am trying to decipher the macros written for a spreadsheet with many tabs. I am copying a line of code which I can not make out:
Range("C" & FirstCopyRow & ":F" & FirstCopyRow + DataRowsperSheet - 1).Formula =
"=SUMIF(All!$A$2:$A$3000,""*""&$C$2&""*""&$B" & FirstCopyRow & ",All!F$2:F$3000)"
There are a couple of more lines code in the same manner.
Range part is simple. What does "All! " mean? I do not see a worksheet with the name "All". Does it mean all the worksheets in the workbook?
I am not sure what the formula will do to the range itself? I searched this forum and could not find a satisfactory answer. Can anyone please explain what it means or direct me to a source where some explanation is available.
Unfortunately I can not share the workbook.
Thanks in advance
- Krishna
mdmackillop
03-21-2008, 04:38 PM
Hi Hrishna,
Rename a worksheet as All. Put this code in a standard module and run it in another sheet. You'll see the resultanr formulae in a block of cells.
Sub Test()
Firstcopyrow = 10
datarowspersheet = 5
Range("C" & Firstcopyrow & ":F" & Firstcopyrow + datarowspersheet - 1).Formula = _
"=SUMIF(All!$A$2:$A$3000,""*""&$C$2&""*""&$B" & Firstcopyrow & ",All!F$2:F$3000)"
End Sub
krishnak
03-25-2008, 03:50 PM
Hi md,
Thanks for the reply. Sorry for the delay in replying.
I created another workbook with two sheets - one sheet is named "All". I entered some values in the ranges A:A and F:F. I entered a simple value of 3 in the "C2" of the second worksheet (Sheet2). If I put a simple comparison operator like "">""&$C$2 I am getting the results in the range specified. But I can not understand the expression -
,""*""&$C$2&""*""&$B" & Firstcopyrow & ". I'll appreciate if you can explain me the expression with some detail.
What does the first multiplications sign (is this a multiplication sign?) do? Or is it some sort of a regular expression?
In my worksheet if I try "">""&C$2 ""*""""3"" for comparison, I must get the sum of all the corresponding values above 9. But I am getting 0 all the time. Probably I am commiting some syntax error.
Please help.
- Krishna
mdmackillop
03-25-2008, 04:09 PM
Sumif is looking for a sting in the format *?*? where * is the literal character. ? could be letter or number
Bob Phillips
03-25-2008, 04:14 PM
The VBA is creating a string to create a formula in a worksheet. The target formula is
"=SUMIF(All!$A$2:$A$3000,"*"&$C$2&"*&$B10,All!F$2:F$3000)"
which looks in A2:A3000 for any cell that CONTAINS the strings in C2 and B10, and if so adds up the corresponding value in B2:B3000.
So if C2 = ABC and B10 = XYZ, the following cells match
- some text ABC some more text XYZ even more text
- some text ABC some more text XYZ
- ABC some more text XYZ even more text
- some text ABCXYZ
- ABCXYZ some text
and so the B values get summed. Thes cells fail
- some text AB C some more text XYZ even more text
- some text ABC some more text X YZ even more text
- some text XYZ some more text ABC even more text
and so the B values are not summed.
mdmackillop
03-25-2008, 04:21 PM
That make much more sense!
krishnak
03-26-2008, 08:56 AM
Hi All,
Thank you very much for a detailed explanation. The "*" notation representing a character makes it clear. I was thinking in terms of the multiplier.
As I mentioned earlier, I am trying to unravel the macros written and handed over by an expert with no explanation whatsoever. I am still to find the sheet named "All" in the workbook and what it does.
Anyway, thanks to you again, good progress is made in understanding the code where I was getting stumped.
- Krishna
Bob Phillips
03-26-2008, 09:05 AM
It might be very hidden.
Try enetering
Worksheets("All").Visible = xlSheetVisible
in the immediate window in the VBIDE
krishnak
03-26-2008, 11:13 AM
Thanks, that is what I did soon after my previous post. I found that two worksheets are hidden.
- Krishna
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.